第二列:在该orders_id下,只有某个units_id下面的所有unit_status_id仅仅为3或13的时候,该units_id才能统计到这一列里面
第二列:在该orders_id下,只要某个units_id下面的所有unit_status_id出现过4或者10,就能统计到这一列里面
这二句什么意思
第二列:在该orders_id下,只要某个units_id下面的所有unit_status_id出现过4或者10,就能统计到这一列里面
这二句什么意思
解决方案 »
- unicode 问题
- 求教一个多个字段一条sql语句查询的问题,谢谢了
- 以时间为条件的查询
- SQL2000中筛选客户对每个商品的最后一次进货日期
- 怎样判断某员工表成绩列既有不及格,又有及格的?
- 求助,数据库数据恢复。
- 检查指定数据库中的系统表内及系统表间的一致性(DBCC CHECKCATALOG)出错,谁能指点迷津?100分奉上。
- 关于SQL SERVER2000使用DTS导出数据的问题?
- 数据库移植!谢谢各位老大!!!
- 各位大神,存储过程查不出数据!求指教!在线等!
- 我在数据库中建立了存储过程sp_N 怎么只有在实验时可以得到数据 好像连接不了数据库?????
- Sql CE数据库的一个数据表最多可以放多少条数据?
units_id=3,4统计在第二列下面,共2个,因为units 3和4的状态只有状态3和状态13
因此对于order_id=2的情况来说,所要的统计结果如下:
orders_id status3/13 status 4/10 status9
2 2 1 0对于其他的order有结果如下:
orders_id status3/13 status 4/10 status9
3 1 1 0
4 1 1 1不知道这样说清楚了没有,抱歉了各位orders_id units_id unit_status_id time
2 2 13 2009-03-26 15:03:00.000
2 2 3 2009-03-26 15:05:01.000
2 2 3 2009-03-26 15:16:30.000
2 2 3 2009-03-26 15:23:00.000
2 2 3 2009-03-26 15:37:00.000
2 2 4 2009-03-26 15:41:00.000
2 2 4 2009-03-26 16:10:37.360
2 2 9 2009-03-26 16:11:06.983
2 2 4 2009-03-27 15:24:24.297
2 2 4 2009-04-09 10:20:51.843
2 3 3 2009-03-26 15:05:27.000
2 3 13 2009-03-26 15:03:28.000
2 3 3 2009-04-01 13:50:47.000
2 4 3 2009-04-01 13:56:17.000
2 4 13 2009-03-26 15:03:47.000
2 4 3 2009-03-26 15:07:15.000
3 5 4 2009-03-26 15:09:01.000
3 5 13 2009-03-26 15:04:18.000
3 5 3 2009-04-01 14:02:17.000
3 6 3 2009-04-01 14:08:17.000
3 6 13 2009-03-26 15:04:54.000
3 6 3 2009-03-26 15:13:01.000
4 7 13 2009-04-01 10:57:47.000
4 7 9 2009-04-01 13:53:30.000
4 7 3 2009-04-01 11:19:24.000
4 7 3 2009-04-01 13:37:47.000
4 8 3 2009-04-01 13:41:47.000
4 8 4 2009-04-01 11:24:04.000
4 8 3 2009-04-01 13:59:30.000
4 8 13 2009-04-01 10:58:13.000
4 9 13 2009-04-01 10:59:01.000
4 9 3 2009-04-01 14:10:30.000
4 9 3 2009-04-01 14:10:30.000
4 9 3 2009-04-01 11:26:04.000
4 9 3 2009-04-01 13:46:47.000
Create table T(orders_id int, units_id int, unit_status_id int)
insert into T
select 2,2,3
union all select 2,2,3
union all select 2,2,4
union all select 2,2,4
union all select 2,2,9
union all select 2,2,4
union all select 2,2,4
union all select 2,3,3
union all select 2,3,13
union all select 2,3,3
union all select 2,4,3
union all select 2,4,13
union all select 2,4,3
union all select 2,5,3
union all select 2,5,13
union all select 2,5,3
union all select 2,6,3
union all select 2,6,13
union all select 2,6,4 --添加測試數據union all select 3,2,4 --添加測試數據
union all select 3,3,3 --添加測試數據
union all select 3,3,9 --添加測試數據GO
Create Function dbo.fn_test(@orders_id int, @a int,@b int,@flag int)
returns varchar(30)
as
begin
declare @re varchar(30)
set @re=''
if @flag=1
begin
select @re=@re+','+rtrim(units_id) from ( select distinct units_id from T where orders_id=@orders_id and unit_status_id in(@a,@b) ) A
end
if @flag=2
begin
select @re=@re+','+rtrim(units_id)
from
(select distinct units_id from T
where orders_id=@orders_id
and exists(select 1 from T a where orders_id=T.orders_id and units_id=T.units_id and unit_status_id in(@a,@b) group by orders_id,units_id having count(distinct unit_status_id)=2 )
and exists(select 1 from T a where orders_id=T.orders_id and units_id=T.units_id group by orders_id,units_id having count(distinct unit_status_id)=2 )
) X
end
if len(@re)>0
set @re=stuff(@re,1,1,'') return @reendGOselect orders_id,
[4or10] = dbo.fn_test(orders_id,4,10,1),
[3and13] = dbo.fn_test(orders_id,3,13,2),
[9] = dbo.fn_test(orders_id,9,99999,1)
From T
group by orders_id
/*
orders_id 4or10 3and13 9
----------- ------------------------------ ------------------------------ ------------------------------
2 2,6 3,4,5 2
3 2 3
*/GO
Drop table T
drop function dbo.fn_test
----------- ------------------------------ ------------------------------ ------------------------------
2 2,6 3,4,5 2
3 2 3另外统计的结果好像有些偏差
orders_id 4or10 3and13 9
----------- ------------------------------ ------------------------------ ------------------------------
2 1(2) 2(3,4) 0
3 1(5) 1(6) 0
4 1(8) 1(9) 1(7)以上为应该出现的结果,第一列为orders_id,低而列为该orders_id下的统计结果,括号里面的值为统计的对象(2)表示units_id=2被统计在这里。
注意:最后结果不需要出现(xx),这里仅作说明用摆脱了
go
create table [tb]([orders_id] int,[units_id] int,[unit_status_id] int,[time] datetime)
insert [tb]
select 2,2,13,'2009-03-26 15:03:00.000' union all
select 2,2,3,'2009-03-26 15:05:01.000' union all
select 2,2,3,'2009-03-26 15:16:30.000' union all
select 2,2,3,'2009-03-26 15:23:00.000' union all
select 2,2,3,'2009-03-26 15:37:00.000' union all
select 2,2,4,'2009-03-26 15:41:00.000' union all
select 2,2,4,'2009-03-26 16:10:37.360' union all
select 2,2,9,'2009-03-26 16:11:06.983' union all
select 2,2,4,'2009-03-27 15:24:24.297' union all
select 2,2,4,'2009-04-09 10:20:51.843' union all
select 2,3,3,'2009-03-26 15:05:27.000' union all
select 2,3,13,'2009-03-26 15:03:28.000' union all
select 2,3,3,'2009-04-01 13:50:47.000' union all
select 2,4,3,'2009-04-01 13:56:17.000' union all
select 2,4,13,'2009-03-26 15:03:47.000' union all
select 2,4,3,'2009-03-26 15:07:15.000' union all
select 3,5,4,'2009-03-26 15:09:01.000' union all
select 3,5,13,'2009-03-26 15:04:18.000' union all
select 3,5,3,'2009-04-01 14:02:17.000' union all
select 3,6,3,'2009-04-01 14:08:17.000' union all
select 3,6,13,'2009-03-26 15:04:54.000' union all
select 3,6,3,'2009-03-26 15:13:01.000' union all
select 4,7,13,'2009-04-01 10:57:47.000' union all
select 4,7,9,'2009-04-01 13:53:30.000' union all
select 4,7,3,'2009-04-01 11:19:24.000' union all
select 4,7,3,'2009-04-01 13:37:47.000' union all
select 4,8,3,'2009-04-01 13:41:47.000' union all
select 4,8,4,'2009-04-01 11:24:04.000' union all
select 4,8,3,'2009-04-01 13:59:30.000' union all
select 4,8,13,'2009-04-01 10:58:13.000' union all
select 4,9,13,'2009-04-01 10:59:01.000' union all
select 4,9,3,'2009-04-01 14:10:30.000' union all
select 4,9,3,'2009-04-01 14:10:30.000' union all
select 4,9,3,'2009-04-01 11:26:04.000' union all
select 4,9,3,'2009-04-01 13:46:47.000'
go
--select * from [tb]select orders_id
,[status3/13]=(select count(distinct(units_id)) from tb t1 where orders_id=t.orders_id and unit_status_id in (3,13)
and not exists(select 1 from tb where orders_id=t1.orders_id and units_id=t1.units_id and unit_status_id not in(3,13)))
,[status4/10]=(select count(distinct(units_id)) from tb t2 where orders_id=t.orders_id and unit_status_id in (4,10)
and not exists(select 1 from tb where orders_id=t2.orders_id and units_id=t2.units_id and unit_status_id=9 and time>t2.time))
,[status9]=(select count(distinct(units_id)) from tb t3 where orders_id=t.orders_id and unit_status_id=9
and not exists(select 1 from tb where orders_id=t3.orders_id and units_id=t3.units_id and unit_status_id in (4,10) and time>t3.time))
from tb t
group by orders_id
/*
orders_id status3/13 status4/10 status9
----------- ----------- ----------- -----------
2 2 1 0
3 1 1 0
4 1 1 1(3 行受影响)
*/
注意:最后结果不需要出现(xx),这里仅作说明用 拜托了 上面好多错别字,重新纠正一下
如果后面再加上一个和值,即为前面三列值的和,怎么写呢?
多谢了!
from
(
select orders_id
,[status3/13]=(select count(distinct(units_id)) from tb t1 where orders_id=t.orders_id and unit_status_id in (3,13)
and not exists(select 1 from tb where orders_id=t1.orders_id and units_id=t1.units_id and unit_status_id not in(3,13)))
,[status4/10]=(select count(distinct(units_id)) from tb t2 where orders_id=t.orders_id and unit_status_id in (4,10)
and not exists(select 1 from tb where orders_id=t2.orders_id and units_id=t2.units_id and unit_status_id=9 and time>t2.time))
,[status9]=(select count(distinct(units_id)) from tb t3 where orders_id=t.orders_id and unit_status_id=9
and not exists(select 1 from tb where orders_id=t3.orders_id and units_id=t3.units_id and unit_status_id in (4,10) and time>t3.time))
from tb t
group by orders_id
) a
/*
orders_id status3/13 status4/10 status9 total
----------- ----------- ----------- ----------- -----------
2 2 1 0 3
3 1 1 0 2
4 1 1 1 3(3 行受影响)
*/