怎么查询 多条记录满足不同值的数据

如何查询 多条记录满足不同值的数据
例如 table

rq cb je xm

2012-07-09 00:00:00晚餐11.9艾凤琴
2012-07-09 00:00:00中餐10.4 艾凤琴
2012-07-09 00:00:00早餐5.2 艾凤琴
2012-07-10 00:00:00早餐4.9 艾凤琴
2012-07-10 00:00:00中餐10.5 艾凤琴
2012-07-10 00:00:00晚餐12 艾凤琴
2011-12-22 00:00:00晚餐6.0 艾胡蓉
2011-12-22 00:00:00中餐3.2 艾胡蓉
2011-12-22 00:00:00早餐 4.2 艾胡蓉
2012-06-24 00:00:00晚餐7.93 白玲
2012-06-24 00:00:00中餐6.89 白玲
2012-06-24 00:00:00早餐4.669 白玲
2012-07-04 00:00:00晚餐8.23 白珊
2012-07-04 00:00:00中餐6.759 白珊
2012-07-04 00:00:00早餐 4.190 白珊
2011-11-20 00:00:00晚餐6.25 班小会
2011-11-20 00:00:00中餐7.37 班小会
2011-11-20 00:00:00早餐 4.280班小会
2011-12-19 00:00:00晚餐7.7 包双双
2011-12-19 00:00:00中餐7.5 包双双
2011-12-19 00:00:00早餐5.04 包双双
2011-12-22 00:00:00晚餐8.6 卜丽玲
2011-12-22 00:00:00中餐7.73、 卜丽玲
2011-12-22 00:00:00早餐 5.629 卜丽玲
2012-04-27 00:00:00晚餐 5.5 卜美华
2012-04-27 00:00:00中餐6.29卜美华
2012-04-27 00:00:00早餐4.2999卜美华

查询要求
1、同一个人同一天符合早餐≥5.0 及中餐≥10.0 的人员
2、同一个人同一天符合早餐≥5.0 及晚餐≥10.0及中餐≥8.0 的人

望高手指点



[解决办法]

SQL code

--1.select convert(varchar(10),a.rq,120) rq,a.xm from(select * from @test where cb=N'早餐' and je>=5) a,(select * from @test where cb=N'中餐' and je>=10) bwhere convert(varchar(10),a.rq,120)=convert(varchar(10),b.rq,120) and a.xm=b.xm--2.select convert(varchar(10),a.rq,120) rq,a.xm from(select * from @test where cb=N'早餐' and je>=5) a,(select * from @test where cb=N'中餐' and je>=8) b,(select * from @test where cb=N'晚餐' and je>=10) cwhere convert(varchar(10),a.rq,120)=convert(varchar(10),b.rq,120)     and convert(varchar(10),a.rq,120)=convert(varchar(10),c.rq,120)    and a.xm=b.xm and a.xm=c.xm
[解决办法]
SQL code
--> 测试数据：[tb]IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]GO CREATE TABLE [tb]([rq] DATETIME,[cb] VARCHAR(4),[je] NUMERIC(6,4),[xm] VARCHAR(6))INSERT [tb]SELECT '2012-07-09 00:00:00','晚餐',11.9,'艾凤琴' UNION ALLSELECT '2012-07-09 00:00:00','中餐',10.4,'艾凤琴' UNION ALLSELECT '2012-07-09 00:00:00','早餐',5.2,'艾凤琴' UNION ALLSELECT '2012-07-10 00:00:00','早餐',4.9,'艾凤琴' UNION ALLSELECT '2012-07-10 00:00:00','中餐',10.5,'艾凤琴' UNION ALLSELECT '2012-07-10 00:00:00','晚餐',12,'艾凤琴' UNION ALLSELECT '2011-12-22 00:00:00','晚餐',6.0,'艾胡蓉' UNION ALLSELECT '2011-12-22 00:00:00','中餐',3.2,'艾胡蓉' UNION ALLSELECT '2011-12-22 00:00:00','早餐',4.2,'艾胡蓉' UNION ALLSELECT '2012-06-24 00:00:00','晚餐',7.93,'白玲' UNION ALLSELECT '2012-06-24 00:00:00','中餐',6.89,'白玲' UNION ALLSELECT '2012-06-24 00:00:00','早餐',4.669,'白玲' UNION ALLSELECT '2012-07-04 00:00:00','晚餐',8.23,'白珊' UNION ALLSELECT '2012-07-04 00:00:00','中餐',6.759,'白珊' UNION ALLSELECT '2012-07-04 00:00:00','早餐',4.190,'白珊' UNION ALLSELECT '2011-11-20 00:00:00','晚餐',6.25,'班小会' UNION ALLSELECT '2011-11-20 00:00:00','中餐',7.37,'班小会' UNION ALLSELECT '2011-11-20 00:00:00','早餐',4.280,'班小会' UNION ALLSELECT '2011-12-19 00:00:00','晚餐',7.7,'包双双' UNION ALLSELECT '2011-12-19 00:00:00','中餐',7.5,'包双双' UNION ALLSELECT '2011-12-19 00:00:00','早餐',5.04,'包双双' UNION ALLSELECT '2011-12-22 00:00:00','晚餐',8.6,'卜丽玲' UNION ALLSELECT '2011-12-22 00:00:00','中餐',7.73,'卜丽玲' UNION ALLSELECT '2011-12-22 00:00:00','早餐',5.629,'卜丽玲' UNION ALLSELECT '2012-04-27 00:00:00','晚餐',5.5,'卜美华' UNION ALLSELECT '2012-04-27 00:00:00','中餐',6.29,'卜美华' UNION ALLSELECT '2012-04-27 00:00:00','早餐',4.2999,'卜美华'--------------开始查询----------------------------1SELECT * FROM [tb] AS t WHERE EXISTS(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='早餐' AND [je]>=5.0 )AND EXISTS(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='中餐' AND [je]>=10.0 )----------------结果----------------------------/* rq                      cb   je                                      xm----------------------- ---- --------------------------------------- ------2012-07-09 00:00:00.000 晚餐   11.9000                                 艾凤琴2012-07-09 00:00:00.000 中餐   10.4000                                 艾凤琴2012-07-09 00:00:00.000 早餐   5.2000                                  艾凤琴(3 行受影响)*/--2SELECT * FROM [tb] AS t WHERE EXISTS(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='早餐' AND [je]>=5.0 )AND EXISTS(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='中餐' AND [je]>=8.0 )AND EXISTS(SELECT 1 FROM tb WHERE [xm]=t.[xm] AND [rq]=t.[rq] AND [cb]='晚餐' AND [je]>=10.0 )----------------结果----------------------------/* rq                      cb   je                                      xm----------------------- ---- --------------------------------------- ------2012-07-09 00:00:00.000 晚餐   11.9000                                 艾凤琴2012-07-09 00:00:00.000 中餐   10.4000                                 艾凤琴2012-07-09 00:00:00.000 早餐   5.2000                                  艾凤琴(3 行受影响)*/