搜索
SQL实际案例分析处理目录数据查询案例...................................................................................................................................3单表查询...................................................................................................................................3取得分组TOP-N..............................................................................................................3取得分组TOP-N的升级版.............................................................................................5寻找连续ID中残缺的数据.............................................................................................7寻找连续日期中残缺的数据...........................................................................................9行列装换--使用CASEWHEN..........................................................................................13行列装换--使用PIVOT....................................................................................................14多行转换为一行--使用通常的方式..............................................................................15多行转换为一行--使用FORXML的方式......................................................................17一行转多行.....................................................................................................................19树形结构.........................................................................................................................21双表查询.................................................................................................................................24区间段查询.....................................................................................................................24报表方式显示.................................................................................................................28多表查询.................................................................................................................................31数据插入案例.................................................................................................................................32数据更新案例.................................................................................................................................33自动递增某一列数据（不使用数据库递增）.....................................................................33两表更新（每一行都可以匹配）.........................................................................................34两表更新（不是每一行都可以匹配）.................................................................................35两表更新（使用VIEW）.......................................................................................................37数据删除案例.................................................................................................................................40数据查询案例单表查询取得分组TOP-N测试表与测试数据CREATETABLETopnTest(nameVARCHAR(10),--姓名procDateDATETIME,--处理时间resultINT--成绩);INSERTINTOTopnTestVALUES('张三','2010-10-0112:00:05',80);INSERTINTOTopnTestVALUES('张三','2010-10-0112:20:05',85);INSERTINTOTopnTestVALUES('张三','2010-10-0207:25:15',79);INSERTINTOTopnTestVALUES('张三','2010-10-0210:30:05',88);INSERTINTOTopnTestVALUES('张三','2010-10-0315:05:05',86);INSERTINTOTopnTestVALUES('李四','2010-10-0106:00:05',60);INSERTINTOTopnTestVALUES('李四','2010-10-0408:00:05',90);INSERTINTOTopnTestVALUES('李四','2010-10-0510:00:05',75);INSERTINTOTopnTestVALUES('李四','2010-10-0811:00:05',88);INSERTINTOTopnTestVALUES('李四','2010-10-0912:00:05',60);INSERTINTOTopnTestVALUES('王五','2010-09-1008:00:05',70);INSERTINTOTopnTestVALUES('王五','2010-09-1408:00:05',80);INSERTINTOTopnTestVALUES('王五','2010-09-2518:00:05',75);INSERTINTOTopnTestVALUES('王五','2010-09-2818:00:05',88);INSERTINTOTopnTestVALUES('王五','2010-10-0912:00:05',70);要求取得每个人的最近2次处理时间的详细记录情况。思路如果仅仅是一个人的最近2次，那么直接TOP2或者Rownum=2可以处理。每个人的最近1次，也可以通过SELECTMAX()GROUPBY来实现。每个人的最近2次，需要自己和自己关联，才能解决了。实现SELECT*FROMTopnTestWHERE(SELECTCOUNT(1)FROMTopnTestsubTopnTestWHERETopnTest.name=subTopnTest.nameANDTopnTest.procDatesubTopnTest.procDate)2ORDERBYname,procDate执行结果nameprocDateresult--------------------------------------------李四2010-10-0811:00:05.00088李四2010-10-0912:00:05.00060王五2010-09-2818:00:05.00088王五2010-10-0912:00:05.00070张三2010-10-0210:30:05.00088张三2010-10-0315:05:05.00086取得分组TOP-N的升级版CREATETABLETEST_TOP1([型号]VARCHAR(10),[品牌]VARCHAR(10),[批号]VARCHAR(10),[数量]INT)goINSERTINTOTEST_TOP1SELECT'ABC','TOKO','2003',1111UNIONALLSELECT'ABC','YAMAHA','2000',1000UNIONALLSELECT'ABC','MOTOROLA','2004',3000UNIONALLSELECT'ABC','INFINEON','2004',3000UNIONALLSELECT'OTHER','Test','2002',1000go1、我需要所有字段2、按型号分组3、取数量最大的那条记录4、数量有多条记录相同时，只取1条SELECT[型号],[品牌],[批号],[数量]FROM(SELECTROW_NUMBER()OVER(PARTITIONBY[型号]ORDERBY[品牌])ASid,TEST_TOP1.*FROMTEST_TOP1WHERE(SELECTCOUNT(1)FROMTEST_TOP1subTopnTestWHERETEST_TOP1.[型号]=subTopnTest.[型号]ANDTEST_TOP1.[数量]subTopnTest.[数量])1)tmpViewWHEREid=1型号品牌批号数量-----------------------------------------ABCINFINEON20043000OTHERTest20021000寻找连续ID中残缺的数据测试表与测试数据CREATETABLEseq_test(IDINT);INSERTINTOseq_testVALUES(1);INSERTINTOseq_testVALUES(2);INSERTINTOseq_testVALUES(3);INSERTINTOseq_testVALUES(6