Sql查询技艺实例
Sql查询技巧
1、 表A04中的含有A、B、C、D四个字段,当按A字段分组后,如果D等1,则只统计B的值,如果D等0,则只统计C的值。
CREATE TABLE A04(A VARCHAR(20),B INT,C INT,D INT)
INSERT INTO A04 VALUES('01',20,7,'0')
INSERT INTO A04 VALUES('01',10,8,'1')
INSERT INTO A04 VALUES('02',20,7,'1')
INSERT INTO A04 VALUES('02',10,8,'0')
SELECT A,SUM(CASE D WHEN 1 THEN B WHEN 0 THEN C END) FROM A04 GROUP BY A
2、 如何实现有一组有规则的编号(如200305310001…200305310999)
DECLARE @I INT,@C VARCHAR(20)
SELECT @I=1
WHILE @I<1000 BEGIN
SELECT @C=CASE WHEN @I<10 THEN '000'+CAST(@I AS CHAR(1))
WHEN @I BETWEEN 10 AND 99 THEN '00'+CAST(@I AS CHAR(2))
WHEN @I BETWEEN 100 AND 999 THEN '0'+CAST(@I AS CHAR(3))
END
SELECT @C=CONVERT(VARCHAR(20),GETDATE(),112)+@C
SELECT @C --在查询分析器中输出
SELECT @I=@I+1
END
3、现在有三个表,结构如下
Score(FScoreId 成绩记录号,FSubID 课程号,FStdID 学生号,FScore 成绩)
student:(FID 学生号,FName 姓名)
subject:(FSubID 课程号,FSubName 课程名),
怎么能实现这个表:
姓名 英语 数学 语文 历史
张萨 78 67 89 76
王强 89 67 84 96
李三 70 87 92 56
李四 80 78 97 66
SELECT A.FNAME AS 姓名,
英语 = SUM(CASE B.FSUBNAME WHEN '英语' THEN C.FSCORE END),
数学 = SUM(CASE B.FSUBNAME WHEN '数学' THEN C.FSCORE END),
语文 = SUM(CASE B.FSUBNAME WHEN '语文' THEN C.FSCORE END),
历史 = SUM(CASE B.FSUBNAME WHEN '历史' THEN C.FSCORE END)
FROM STUDENT A, SUBJECT B, SCORE C
WHERE A.FID = C.FSTDID AND B.FSUBID = C.FSUBID GROUP BY A.FNAME
4、有两个表,用一条SQL语句算出商品A,B目前还剩多少?表结构如下:
商品名称mc 商品总量sl 表一(AAA)
A 100
B 120
商品名称mc 出库数量sl 表二(BBB)
A 10
A 20
B 10
B 20
SELECT TA.商品名称,A-B AS 剩余数量 FROM
(SELECT 商品名称,SUM(商品总量) AS A FROM AAA GROUP BY 商品名称)TA,
(SELECT 商品名称,SUM(出库数量) AS B FROM BBB GROUP BY 商品名称)TB
WHERE TA.商品名称=TB.商品名称
6、有一个商品销售表,记载了某月商品的销售数量,现在要为所有商品排名次,放到一个单独的字段中,就是说达到右边显示的效果,如何作?
BU1032 5 NULL BU1032 5 2
PS2091 3 NULL PS2092 3 3
PC8888 50 NULL PC8888 50 1
UPDATE X SET ORD=(SELECT COUNT(*)+1 FROM X B WHERE B.QTY>X.QTY)
7、本人有一张表单, 要求统计COL1~COL6中不等于2的列的个数,数据如下:
————————————————————————————————
ROW_ID | COL1 | COL2 | COL3 | COL4 | COL5 | COL6 |
1 | 2 | 1 | 1 | 2 | 3 | 2 |
2 | 1 | 1 | 2 | 2 | 2 | 2 |
3 | 2 | 3 | 2 | 2 | 1 | 2 |
4 | 2 | 2 | 2 | 2 | 1 | 2 |
5 | 1 | 2 | 2 | 2 | 2 | 2 |
6 | 2 | 2 | 2 | 2 | 2 | 1 |
————————————————————————————————
要求结果如下:
—————————
ROW_ID | COUNT |
1 | 3 |
2 | 2 |
3 | 2 |
4 | 1 |
5 | 1 |
6 | 1 |
SELECT ROW_ID,(6-(CASE WHEN COL1=2 THEN COL1 / 2 ELSE 0 END)
-(CASE WHEN COL2=2 THEN COL2 / 2 ELSE 0 END)
-(CASE WHEN COL3=2 THEN COL3 / 2 ELSE 0 END)
-(CASE WHEN COL4=2 THEN COL4 / 2 ELSE 0 END)
-(CASE WHEN COL5=2 THEN COL5 / 2 ELSE 0 END)
-(CASE WHEN COL6=2 THEN COL6 / 2 ELSE 0 END))AS COUNT FROM TABLE_A
8、有一客户表,数据如下:
客户 日期 资金
F001 2003-03-05 123.00
F002 2003-03-04 1223.00
F002 2003-03-02 1123.00
F003 2003-03-05 1231.00
F003 2003-03-04 1232.00
要求选出每个客户最新的哪条记录 组成一个结果集,结果如下:
F001 2003-03-05 123.00
F002 2003-03-04 1223.00
F003 2003-03-05 1231.00
SELECT A.客户, B.日期, A.资金 FROM 客户资金表 A,
(SELECT 客户, MAX(日期) 日期 FROM 客户资金表 GROUP BY 客户 ) B
WHERE A.客户 = B.客户 AND A.日期 = B.日期
9、用游标将根据sys_orgmenber表中的member字段为依据将org字段更新到sys_user 表中username对应的reg_num字段信息 ?
10、下表,记录数据较多,要求对同一分类的数据进行排序?
CREATE TABLE A_TEST(工號VARCHAR(10), 姓名 varchar(10), 時間VARCHAR(20), 序號INT)
INSERT INTO A_TEST VALUES('001',' abc ',’17:40’,0)
INSERT INTO A_TEST VALUES('001','abc',’08:00’,0)
INSERT INTO A_TEST VALUES('001',' abc ',’13:28’,0)
INSERT INTO A_TEST VALUES('001',' abc ',’12:01’,0)
INSERT INTO A_TEST VALUES('002','def',’07:30’,0)
INSERT INTO A_TEST VALUES('002','def',’22:59’,0)
结果
工號 姓名 時間 序號
001 abc 08:00 1
001 abc 12:01 2
001 abc 13:28 3
001 abc 17:40 4
002 def 07:30 1
002 def 22:59 2
UPDATE A_TEST SET ORD=(
SELECT COUNT(*)+1 FROM A_TEST B WHERE B.SJ<A_TEST.SJ AND B.ID=A_TEST.ID)
11、学生关系:S(Sno,Sname,Ssex,Sage,class)
Sno-学号,class-班级,Sname-姓名,Ssex-性别,Sage-年龄
问题:列出至少有12名男生的班号。
Select Class
FROM S
GROUP BY class,Ssex
HAVING (COUNT(Class)>=12) AND (Ssex='男')
12、计算一个月第一天的SQL 脚本,周(wk)的时间间隔来计算哪一天是本周的星期一,用年(yy)的时间间隔来显示这一年的第一天,季度的第一天
select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
13、原纪录:
11 AA,BB,CC,DD
想变成
11 AA
11 BB
11 CC
11 DD
怎么写语句?
create table tb其不意(id int,name varchar(500))
insert tb其不意 values(11,'AA,BB,CC,DD')
select top 500 id=identity(int,1,1) into # from syscolumns a,syscolumns b
select *
from
(
select a.id,name=substring(a.name,b.id,charindex(',',a.name+',',b.id)-b.id)
from tb其不意 a,# b
where b.id<=len(a.name) and substring(','+a.name,b.id,1)=','
) x
drop table tb其不意,#