Home Database Mysql Tutorial SQL Server 搜索技巧随记

SQL Server 搜索技巧随记

Jun 07, 2016 pm 03:16 PM
server sql Skill search project

平时做项目过程中,会遇到各种问题,如 SQL Server 中的查询,有时一些棘手的问题,可能最终解决,但会费很大的劲,但如果了解某些技巧,却能事半功倍。 1、空记录的处理。 之前在做机房收费系统时,日结账单中,需要对数据汇总,但汇总结果可能为Null,那么

        平时做项目过程中,会遇到各种问题,如 SQL Server 中的查询,有时一些棘手的问题,可能最终解决,但会费很大的劲,但如果了解某些技巧,却能事半功倍。


1、空记录的处理。

        之前在做机房收费系统时,日结账单中,需要对数据汇总,但汇总结果可能为Null,那么如何把这种情况下的Null转成0呢?在客户端程序中进行处理?那肯定比较费事!有没有更好的办法?——这时可以用到Isnull(,)函数。如:
-- =============================================
-- Author:		张连海
-- Create date: 2013.09.28
-- Description:	用于机房收费系统中结账,查询相应的信息
-- =============================================
ALTER procedure [dbo].[Proc_SelectAccounts]
	@operateUserName varchar(20),
	@isSettleAccounts varchar(10)
as
begin
	select
		(select isnull(count(cardNo),0) from T_Register where regUser =@operateUserName and isSettleAccounts=@isSettleAccounts) as '售卡张数',
		(select isnull(count(cardNo),0) from T_AbsentCard where abCardUser =@operateUserName and isSettleAccounts=@isSettleAccounts) as '退卡张数',
		(select isnull(sum(recMoney),0) from T_Recharge where recUser=@operateUserName and isSettleAccounts=@isSettleAccounts) as '充值金额',
		(select isnull(sum(abCardMoney),0) from T_AbsentCard where abCardUser=@operateUserName and isSettleAccounts=@isSettleAccounts) as '退卡金额',
		(select isnull(count(cardNo),0) from T_Register where regUser =@operateUserName) as '总售卡张数'
End
Copy after login

2.insert一句话实现插入一条数据并且返回这条数据的某列 。
        在做“善良公社”项目时,用到关系表,其中在往别的表里添加数据时,插入一条数据之后(ID为自动增长列),返回该条数据的ID,再存入该关系表。最好插入和返回不存在时间差。即:不选择先INSERT然后再SELECT,有无根据sql server机制,直接插入的同时返回。

<span>--可使用如下代码:</span>
insert into [table] output inserted.columnName values()
--举例:
insert into 用户信息表 (name) output inserted.id values(张三)
Copy after login

3.实现是1 或0 想显示为男或女

        数据库对于数据的存储可能会用到某种标记,比如“1”表示“男”,“0”表示“女”;“Y”表示“已结账”,“N”表示“未结账”,如何在查询时进行自动转换?

select name,Sex=(case Sex when '1' then '男' when '0' then '女' end) from Tablename
Copy after login

4.按姓氏笔画排序

        我们在书籍的鸣谢中或参与人员中,经常看到人名是按姓氏笔画进行排序的。那么SQL Server中如何实现?

Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
Copy after login


5.……………………

        

        学习在继续,随记在继续。点点滴滴,相信定会汇成江海!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What is the difference between HQL and SQL in Hibernate framework? What is the difference between HQL and SQL in Hibernate framework? Apr 17, 2024 pm 02:57 PM

HQL and SQL are compared in the Hibernate framework: HQL (1. Object-oriented syntax, 2. Database-independent queries, 3. Type safety), while SQL directly operates the database (1. Database-independent standards, 2. Complex executable queries and data manipulation).

Can AI conquer Fermat's last theorem? Mathematician gave up 5 years of his career to turn 100 pages of proof into code Can AI conquer Fermat's last theorem? Mathematician gave up 5 years of his career to turn 100 pages of proof into code Apr 09, 2024 pm 03:20 PM

Fermat's last theorem, about to be conquered by AI? And the most meaningful part of the whole thing is that Fermat’s Last Theorem, which AI is about to solve, is precisely to prove that AI is useless. Once upon a time, mathematics belonged to the realm of pure human intelligence; now, this territory is being deciphered and trampled by advanced algorithms. Image Fermat's Last Theorem is a "notorious" puzzle that has puzzled mathematicians for centuries. It was proven in 1993, and now mathematicians have a big plan: to recreate the proof using computers. They hope that any logical errors in this version of the proof can be checked by a computer. Project address: https://github.com/riccardobrasca/flt

Win11 Tips Sharing: Skip Microsoft Account Login with One Trick Win11 Tips Sharing: Skip Microsoft Account Login with One Trick Mar 27, 2024 pm 02:57 PM

Win11 Tips Sharing: One trick to skip Microsoft account login Windows 11 is the latest operating system launched by Microsoft, with a new design style and many practical functions. However, for some users, having to log in to their Microsoft account every time they boot up the system can be a bit annoying. If you are one of them, you might as well try the following tips, which will allow you to skip logging in with a Microsoft account and enter the desktop interface directly. First, we need to create a local account in the system to log in instead of a Microsoft account. The advantage of doing this is

WPS table cannot find the data you are searching for, please check the search option location WPS table cannot find the data you are searching for, please check the search option location Mar 19, 2024 pm 10:13 PM

In the era dominated by intelligence, office software has also become popular, and Wps forms are adopted by the majority of office workers due to their flexibility. At work, we are required not only to learn simple form making and text entry, but also to master more operational skills in order to complete the tasks in actual work. Reports with data and using forms are more convenient, clear and accurate. The lesson we bring to you today is: The WPS table cannot find the data you are searching for. Why please check the search option location? 1. First select the Excel table and double-click to open it. Then in this interface, select all cells. 2. Then in this interface, click the &quot;Edit&quot; option in &quot;File&quot; in the top toolbar. 3. Secondly, in this interface, click &quot;

What are the tips for novices to create forms? What are the tips for novices to create forms? Mar 21, 2024 am 09:11 AM

We often create and edit tables in excel, but as a novice who has just come into contact with the software, how to use excel to create tables is not as easy as it is for us. Below, we will conduct some drills on some steps of table creation that novices, that is, beginners, need to master. We hope it will be helpful to those in need. A sample form for beginners is shown below: Let’s see how to complete it! 1. There are two methods to create a new excel document. You can right-click the mouse on a blank location on the [Desktop] - [New] - [xls] file. You can also [Start]-[All Programs]-[Microsoft Office]-[Microsoft Excel 20**] 2. Double-click our new ex

A must-have for veterans: Tips and precautions for * and & in C language A must-have for veterans: Tips and precautions for * and & in C language Apr 04, 2024 am 08:21 AM

In C language, it represents a pointer, which stores the address of other variables; & represents the address operator, which returns the memory address of a variable. Tips for using pointers include defining pointers, dereferencing pointers, and ensuring that pointers point to valid addresses; tips for using address operators & include obtaining variable addresses, and returning the address of the first element of the array when obtaining the address of an array element. A practical example demonstrating the use of pointer and address operators to reverse a string.

VSCode Getting Started Guide: A must-read for beginners to quickly master usage skills! VSCode Getting Started Guide: A must-read for beginners to quickly master usage skills! Mar 26, 2024 am 08:21 AM

VSCode (Visual Studio Code) is an open source code editor developed by Microsoft. It has powerful functions and rich plug-in support, making it one of the preferred tools for developers. This article will provide an introductory guide for beginners to help them quickly master the skills of using VSCode. In this article, we will introduce how to install VSCode, basic editing operations, shortcut keys, plug-in installation, etc., and provide readers with specific code examples. 1. Install VSCode first, we need

What is the Excel search shortcut key? What is the Excel search shortcut key? Mar 20, 2024 am 10:52 AM

Want to know what the Excel search shortcut key is? The answer is simple, just use [Ctrl+F] to perform a quick search. We often use Excel to record a lot of different data and names. If we need to change it, it will be inconvenient for us to search because there is too much data, and it will be quite troublesome to search. In fact, we can use a quick search shortcut to find the data we need to change! So, what is the search shortcut key in Excel? Today I’m going to teach you how to use the shortcut search keys in excel tables to save you time and effort in your work. Here I am using this version of Microsoft Office excel 2010 for demonstration. Students who need it can

See all articles