ms sqlserver查询语句 高手进
三张关联表综合的查询。
首先,TB_Card c为总表,TB_CrabCard card和TB_SeaFoodCard seafood为TB_Card的关联表
其中c.ID=card.Pid,c.ID=seafood.Pid。c.Kind分为2种
c.kind=1时,从card表中找数据,c.kind=2时,从seafood表中找数据
下边是三张表的表结构和使用数据
create table TB_Card
(
ID int identity primary key,
UserName nvarchar(200),
[PassWord] nvarchar(200),
[Status] int,
Kind int,
IsSpecial bit,
SpecialName nvarchar(200),
AddTime datetime default(getdate())
)
create table TB_CrabCard
(
ID int identity primary key,
Pid int,--关联TB_Card中的Id--
CardName nvarchar(200),
Price decimal(8,1),
CardNum nvarchar(10),
CardType nvarchar(10),
CardSize nvarchar(200),
IsSpecial bit,
SpecialName nvarchar(200)
)
create table TB_SeaFoodCard
(
ID int identity primary key,
Pid int,--关联TB_Card中的ID--
Name nvarchar(200),
Price decimal(8,1),
IsSpecial bit,
SpecialName nvarchar(200),
SpecialNum nvarchar(10),
SpecialType nvarchar(10),
SpecialSize nvarchar(200)
)
--使用数据--
insert into TB_Card values('A79800001','012356w2',1,2,1,'2013-12-12') --ID=1
insert into TB_Card values('A79800002','d2454834',1,2,1,'2013-12-12') --ID=2
insert into TB_Card values('A79800009','c5487348',1,2,1,'2013-12-12') --ID=3
insert into TB_Card values('A79800009','3d543168',1,2,1,'2013-12-12') --ID=4
insert into TB_Card values('x79800001','012356w2',1,1,1,'2013-12-12') --ID=5
insert into TB_Card values('x79800002','d2454834',1,1,1,'2013-12-12') --ID=6
insert into TB_Card values('x79800009','c5487348',1,1,1,'2013-12-12') --ID=7
insert into TB_Card values('x79800009','3d543168',1,1,1,'2013-12-12') --ID=8
insert into TB_SeaFoodCard values('1','海鲜大礼包A套餐',798.0,1,'798蟹卡','3','798','雌蟹3只 雄蟹3只')
insert into TB_SeaFoodCard values('2','海鲜大礼包B套餐',1150.0,1,'1150蟹卡','3','1150','雌蟹4只 雄蟹4只')
insert into TB_SeaFoodCard values('3','海鲜大礼包C套餐',1980.0,1,'1980蟹卡','3','1980','雌蟹5只 雄蟹5只')
insert into TB_SeaFoodCard values('4','海鲜大礼包D套餐',2780.0,1,'2780蟹卡','3','2780','雌蟹6只 雄蟹6只')
insert into TB_CrabCard values('5','蟹A套餐',798.0,'3','798','雌蟹3只 雄蟹3只',1,'海鲜大礼包A套餐')
insert into TB_CrabCard values('6','蟹B套餐',1150.0,'4','1150','雌蟹4只 雄蟹4只',1,'海鲜大礼包B套餐')
insert into TB_CrabCard values('7','蟹C套餐',1980.0,'5','1980','雌蟹5只 雄蟹5只',1,'海鲜大礼包C套餐')
insert into TB_CrabCard values('8','蟹D套餐',2780.0,'6','2780','雌蟹6只 雄蟹6只',1,'海鲜大礼包D套餐')
