表A
ID CODE NAME
1 001 土豆丝
2 002 炒黄瓜
表B
ID CODE Type StartDate EndData
1 001 0 2012-5-11 2012-5-30
2 002 1 2012-5-10 2012-5-20
显示
表C
ID CODE NAME 推荐菜 开始时间 结束时间 特价菜 开始时间 结束时间
1 001 土豆丝 TRUE 2012-5-10 2012-5-20 NULL NULL NULL
2 002 炒黄瓜 NULL NULL NULL TRUE 2012-5-10 2012-5-30
PS： 表B 0 表示推荐菜 1 表示 特价菜
------解决方案--------------------
- SQL code
if object_id('[TB1]') is not null drop table [TB1]gocreate table [TB1] (ID int,CODE nvarchar(6),NAME nvarchar(6))insert into [TB1]select 1,'001','土豆丝' union allselect 2,'002','炒黄瓜'if object_id('[TB2]') is not null drop table [TB2]gocreate table [TB2] (ID int,CODE nvarchar(6),Type int,StartDate datetime,EndDate datetime)insert into [TB2]select 1,'001',0,'2012-5-11','2012-5-30' union allselect 2,'002',1,'2012-5-10','2012-5-20'select * from [TB1]select * from [TB2]SELECT A.id , A.code , A.NAME , CASE WHEN B.TYPE = 0 THEN 'True' ELSE '' END '推荐菜' , CASE WHEN B.TYPE = 0 THEN B.startdate ELSE NULL END AS '开始时间0', CASE WHEN B.TYPE = 0 THEN B.enddate ELSE NULL END AS '结束时间0', CASE WHEN B.TYPE = 1 THEN 'True' ELSE '' END '特价菜' , CASE WHEN B.TYPE = 1 THEN B.startdate ELSE NULL END AS '开始时间1', CASE WHEN B.TYPE = 1 THEN B.enddate ELSE NULL END AS '结束时间1'FROM TB1 A INNER JOIN TB2 B ON A.code = b.code /* id code NAME 推荐菜 开始时间0 结束时间0 特价菜 开始时间1 结束时间1----------- ------ ------ ---- ----------------------- ----------------------- ---- ----------------------- -----------------------1 001 土豆丝 True 2012-05-11 00:00:00.000 2012-05-30 00:00:00.000 NULL NULL2 002 炒黄瓜 NULL NULL True 2012-05-10 00:00:00.000 2012-05-20 00:00:00.000(2 row(s) affected)*/