一个比较复杂的SQL！

silentwins 2009-09-18 12:02:35
有如下数据:
Table Test | Table TestValue
TestID ProductID StartTime Pass | TestValueID TestID Bin
1 11 2009-09-11 12:34:56 0 | 111 1 9.10
2 12 2009-09-11 12:34:57 1 | 112 2 0.00
3 11 2009-09-11 13:00:00 1 | 113 3 0.00
4 12 2009-09-11 12:34:57 1 | 114 4 0.00
5 13 2009-09-11 13:00:00 0 | 115 5 5.11
6 13 2009-09-11 14:01:00 0 | 116 6 5.11
7 13 2009-09-11 14:33:00 0 | 117 7 10.06

8 13 2009-09-12 11:34:22 1 | 118 8 0.00
9 14 2009-09-12 14:56:00 0 | 119 9 7.33


Expected Table Result
Date TotalTest FirstPass RetestPass Fail TotalPass Bin0.00 Bin7.33 Bin10.06
------------------------------------------------------------------------------------------------------------
2009-09-11 3 1 1 1 2 2 0 1
2009-09-12 2 1 0 1 1 1 1 0



描述：
1.Test表跟TestValue的数据是根据TestID一一对应的，有人会说TestValue表冗余，其实这个表很多字段，也很少用到，不多说了。

2.最终测试是否Pass是按当天的最后结果来算，例如Product 13，虽然在9月12日测试Pass了，但它在9月11日最后一次的测试Fail了，所以它在9月11日算Fail，9月12日算Pass，会占2条统计数据。

3.TotalTest就是当天ProductID的数量，FirstPass是首次测试就Pass的数量（像Product 11会有可能多次测试都Pass的，之后的统计也只能算1条），RetestPass就是当天最终测试Pass的数量，Fail就是当天最终测试Fail的数量，TotalPass就是FirstPass+RetestPass。

4.Bin值统计按照当天最后一次测试的Bin值来统计，这个值是从0.00--##.##都有可能出现，所以要动态生成统计字段了，这个我觉得最复杂了……！！！


本人SQL比较菜，不好意思，确实是来求代码的，当然大家有好的思路提供给我也很欢迎，大家慢慢来，有好的结果就最好了，下星期一准时结帖，谢谢！
...全文
462 40 打赏 收藏 转发到动态 举报
写回复
用AI写文章
40 条回复
切换为时间正序
请发表友善的回复…
发表回复
IAMQUICK 2009-10-13
  • 打赏
  • 举报
 回复
e....
梁兄或者别人能不能解释下梁兄代码的意思啊~新手看不懂啊~
silentwins 2009-09-21
  • 打赏
  • 举报
 回复
对了，用的是liangCK兄的代码，就改成自己的表名，失败了>_<!
silentwins 2009-09-21
  • 打赏
  • 举报
 回复
我自己执行了一下，出现了一个错误，所有bin值统计数据都为0，不解中……

Date       TotalTest   FirstPass   RetestPass  Fail        TotalPass   Bin0.00     Bin21.09    Bin24.12    Bin8.04     Bin9.26     Bin9.27     

---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 

2009-08-06 10          6           2           2           8           0           0           0           0           0           0

2009-08-12 10          8           0           2           8           0           0           0           0           0           0

2009-08-14 12          8           0           4           8           0           0           0           0           0           0



Server: Msg 8115, Level 16, State 8, Line 1

Arithmetic overflow error converting numeric to data type numeric.
sdlyczl 2009-09-21
  • 打赏
  • 举报
 回复
学习
jimwoo 2009-09-21
  • 打赏
  • 举报
 回复
有相同时间那就不能用时间判断最后一次了
--测试数据

declare @Test table(testid int, productid int, starttime datetime, pass tinyint)

insert @test

select 1,      11,        '2009-09-11 12:34:56',  0 union all

select 2,      12,        '2009-09-11 12:34:57',  1 union all

select 3,      11,        '2009-09-11 13:00:00',  1 union all

select 4,      12,        '2009-09-11 12:34:57',  1 union all

select 5,      13,        '2009-09-11 13:00:00',  0 union all

select 6,      13,        '2009-09-11 14:01:00',  0 union all

select 7,      13,        '2009-09-11 14:33:00',  0 union all

select 8,      13,        '2009-09-12 11:34:22',  1 union all

select 9,      14,        '2009-09-12 14:56:00',  0



declare @TestValue table(testvalueid int, testid int, bin decimal(4,2))

insert @testvalue

select 111,        1,      9.10 union all

select 112,        2,      0.00 union all

select 113,        3,      0.00 union all

select 114,        4,      0.00 union all

select 115,        5,      5.11 union all

select 116,        6,      5.11 union all

select 117,        7,      10.06 union all

select 118,        8,      0.00 union all

select 119,        9,      7.33



--临时表1

select convert(char(10), starttime, 120) as test_date, productid,

  min(testid) as first_id, max(testid) as last_id

  into # from @test

  group by convert(char(10), starttime, 120), productid



--临时表2

select a.firstpass, a.retestpass, a.fail, a.totalpass, b.* into #1 from 

(

select test_date,

  sum(firstpass*retestpass) as firstpass,

  sum(case when firstpass = 0 and retestpass = 1 then 1 else 0 end) as retestpass,

  sum(case retestpass when 0 then 1 else 0 end) as fail,

  sum(retestpass) as totalpass

from

(

select a.test_date, a.productid, b.pass as firstpass, c.pass as retestpass

  from # a

  join @test b on a.productid = b.productid and a.first_id = b.testid

  join @test c on a.productid = c.productid and a.last_id = c.testid

) test

group by test_date

) a join

(

select a.test_date, c.bin

  from # a

  join @test b on a.productid = b.productid and a.last_id = b.testid

  join @testvalue c on b.testid = c.testid

) b on a.test_date = b.test_date



--动态SQL

declare @s varchar(8000)

set @s='select test_date, firstpass + retestpass + fail as totaltest, firstpass, retestpass, fail, totalpass'

select @s=@s+', sum(case bin when ' + convert(varchar, bin) + ' then 1 else 0 end) [bin' + convert(varchar, bin) + ']'

  from #1

  group by bin

  order by bin

set @s=@s+' from #1 group by test_date, firstpass, retestpass, fail, totalpass'

exec(@s)

/*

test_date  totaltest   firstpass   retestpass  fail        totalpass   bin0.00     bin7.33     bin10.06    

---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 

2009-09-11 3           1           1           1           2           2           0           1

2009-09-12 2           1           0           1           1           1           1           0

*/
fanzhouqi 2009-09-21
  • 打赏
  • 举报
 回复
学习
silentwins 2009-09-21
  • 打赏
  • 举报
 回复
精辟，谢谢！
liangCK 2009-09-21
  • 打赏
  • 举报
 回复
[Quote=引用 35 楼 silentwins 的回复:]
Bin是varchar
Pass是bit

不执行bin值统计的时候没错，就是在exec '+@str+' 这段出现的错误。
[/Quote]

Bin是varchar的话,就要在动态生成列的时候.用单引号将它括起来了..


--改这样



SELECT @str = @str + ',SUM(CASE WHEN D.Bin='''+RTRIM(Bin)

                     +''' THEN 1 ELSE 0 END) AS [Bin'+RTRIM(Bin)+']'

FROM #TestValue AS A

WHERE EXISTS(SELECT * FROM #tmp WHERE A.TestID = MaxTestID)

GROUP BY Bin;
alonso_hu 2009-09-21
  • 打赏
  • 举报
 回复
学习ing。。关注
silentwins 2009-09-21
  • 打赏
  • 举报
 回复
Bin是varchar
Pass是bit

不执行bin值统计的时候没错，就是在exec '+@str+' 这段出现的错误。
liangCK 2009-09-21
  • 打赏
  • 举报
 回复
TestValue的Bin列的数据类型是什么?
liangCK 2009-09-21
  • 打赏
  • 举报
 回复
TestValue列的数据类型是什么?

如果只执行:

EXEC('SELECT A.[Date],COUNT(*) AS TotalTest,

          SUM(B.Pass) AS FirstPass,

          SUM(CASE WHEN C.Pass=1 AND B.Pass<>C.Pass THEN 1 ELSE 0 END) AS RetestPass,

          SUM(A.Fail) AS Fail,

          SUM(B.Pass)+SUM(CASE WHEN C.Pass=1 AND B.Pass<>C.Pass THEN 1 ELSE 0 END) AS TotalPass

      FROM #tmp AS A

          LEFT JOIN #Test AS B

      ON A.StartTime = B.StartTime AND A.ProductID = B.ProductID

           AND A.MinTestID = B.TestID

          LEFT JOIN #Test AS C

      ON A.EndTime = C.StartTime AND A.ProductID = C.ProductID

           AND A.MaxTestID = C.TestID

          LEFT JOIN #TestValue AS D

      ON A.MaxTestID = D.TestID

      GROUP BY A.[Date]');
不看Bin时,结果是否正确?
xuejiecn 2009-09-19
  • 打赏
  • 举报
 回复
或者多发几个纯散分帖。
xuejiecn 2009-09-19
  • 打赏
  • 举报
 回复
把你的帖子再发到你的论坛一下，让他们再答一遍，呵呵。
appleller 2009-09-19
  • 打赏
  • 举报
 回复
慢慢学习
fire_wang 2009-09-19
  • 打赏
  • 举报
 回复
学习，高手啊，我什么时候才能到你们的境界……
silentwins 2009-09-19
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 js_szy 的回复:]
SQL code
2点不明白：1、
RetestPass就是当天最终测试Pass的数量，为什么11号的是1，而不是23112009-09-1113:00:0014122009-09-1112:34:5712、BIN下的数据时怎么来的？而且列为什么只有这3个？
Bin0.00 Bin7.33 Bin10.06
[/Quote]

1。RetestPass，根据字面可以猜测其意思，就是重测通过，就是第一次测试没通过，后面再测通过了的意思
2。第4点描述说过了，Bin值统计按照某个Product当天最后一次测试的Bin值来统计，这个Product测了n次，只看最后一个结果。
SQL77 2009-09-19
  • 打赏
  • 举报
 回复
学习来的
silentwins 2009-09-19
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 jimwoo 的回复:]
test表中2，4数据是不是重复了？
[/Quote]

不重复，测试完了无论通过与否还是可以再测试的，因为这2条都是一样的数据，所以算第一次就通过了！
silentwins 2009-09-19
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 soft_wsx 的回复:]
好像没有描述清楚，楼主能整理一下吗？另外把分加到300
[/Quote]

不是我吝啬，你看我在SQL的等级，我这帖子1分都加不进去啊……
谢谢大家，我看看先，星期一准时结帖！
加载更多回复（20）
Toad for SQL Server 6.1，最新版绿色单文件（已注册）
最近折腾SQL Server的localdb，微软自带的管理工具很庞大，安装复杂。发现Toad的很好用SQL Server数据库管理工具，特意制作成单文件，不用安装，直接管理。已经注册好了，可以直接使用。win7、8（64位）测试可行。 ...
Sql优化之复杂sql简化拆分
逻辑比较复杂时，写出的sql有时候会一层套一层，又是and又是or，特别难以理解。这些sql往往执行起来性能会有问题，Oracle内核解释起来也比较费劲，好多索引用不上，这时候不要灰心，试试能不能把复杂的sql拆分成几个...
7天带你玩转Mysql数据库之SQL语句
稍微复杂⼀点的SQL就不知道该如何写了。 4. 看不懂同⾏写的SQL是为什么。 5. 不知道索引的原理。 6. 不知道怎么加索引能够提⾼查询性能 纵观全网的教程都只停留在概念解析的层面，没有深入剖析原理，所以看了那么多...
SpringData JPA中复杂sql查询总结
SpringData JPA框架中本人比较喜欢用以下几种方式进行复杂sql查询，大多数场景下足够用了。 方式1（Query注解,hql语局，适用于查询指定条件的数据） 分页查询： //可以自定义整个实体(Page<User>)，也可以查询...
常见复杂SQL语句
1、SQL统计某字段的出现次数 比如统计某个表中，姓名出现的次数： select name,count(*) from biao group by name having count(*) &gt; 2 关键是用分组：group by，且经常和聚合函数一起使用 比如：统计...
MS-SQL Server

34,593

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章