一个查询语句问的题
Table:
ConsInfo://常量表
ConsCode(varchar(20)) ConsType(varchar(20)) ............
00 Kind
01 Kind
00 Type
01 Type
ItemInfo://项目表
ItemCode(varchar(20)) ItemName(varhcar(20))............
000 AA
001 BB
ItemRule://项目规则表
Order(int) ItemType ItemCode .................
1 00 000
2 00 001
3 01 000
4 01 001
筛选ItemRule过后想要获得的记录的字段：
Order ItemType ItemCode ItemName
当ItemRule.ItemType = ConsInfo.ConsCode and Where ConsType= 'Kind ' and ConsCode = '00 '
向ItemName里添加的记录是通过ItemInfo表（where ItemRule.ItemCode = ItemInfo.ItemCode)条件获取，如果是其他条件ItemName字段列插入: "---- ".
最后获取记录达到的效果是:
Order ItemType ItemCode ItemName
1 00 000 AA
2 00 001 BB
3 01 000 ---
4 01 001 ---
[解决办法]
create table ConsInfo(ConsCode varchar(20), ConsType varchar(20))
insert ConsInfo select '00 ', 'Kind '
union all select '01 ', 'Kind '
union all select '00 ', 'Type '
union all select '01 ', 'Type '
create table ItemInfo(ItemCode varchar(20), ItemName varchar(20))
insert ItemInfo select '000 ', 'AA '
union all select '001 ', 'BB '
create table ItemRule([Order] int, ItemType varchar(20), ItemCode varchar(20))
insert ItemRule select 1, '00 ', '000 '
union all select 2, '00 ', '001 '
union all select 3, '01 ', '000 '
union all select 4, '01 ', '001 '
select ItemRule.*, ItemName=isnull(tmp. ItemName, '-- ') from ItemRule left join
(
select ItemRule.*, ItemInfo.ItemName from ItemRule
inner join ConsInfo on ItemRule.ItemType=ConsInfo.ConsCode and ConsType= 'Kind ' and ConsCode= '00 '
inner join ItemInfo on ItemRule.ItemCode=ItemInfo.ItemCode
) tmp on ItemRule.[Order]=tmp.[Order]
--result
Order ItemType ItemCode ItemName
----------- -------------------- -------------------- --------------------
1 00 000 AA
2 00 001 BB
3 01 000 --
4 01 001 --
(4 row(s) affected)
