ÇóÒ»ÌõSQLÓï¾ä¡£ÈçÏ - .NET¼¼Êõ / C#

ÇóÒ»ÌõSQLÓï¾ä¡£ÈçÏÂ
±í a
id Ö÷¼ü
²¿ÃÅ id
Ô±¹¤ÐÕÃû
ѧÀú

±í b
id Ö÷¼ü
²¿ÃÅÃû

±í c
ѧÀú id
ѧÀúÃû

Çó±í
  ²¿ÃÅa ²¿ÃÅb ²¿ÃÅc ²¿ÃÅd
³õÖÐ x x x x 
¸ßÖÐ x x x x
´óר x x x x
´óѧ x x x x

ͳ¼Æ ÈËÊý

---

°ï¶¥~~

---

x x x x  
ÊÇ´ú±íÈËÊý°É¡£

---

x´ú±íÈËÊý

---

select b.*,c.*,(select count(0) from a where ²¿ÃÅId=b.id and ѧÀúid=c.id) as count from b,c

ÏÈÖ´Ðп´¿´°É

---

²»Ðа¡¡£ÐÖµÜ

---

ʲôÊý¾Ý¿â£¿Õâ²»ÊǵäÐ͵Ä͸ÊÓ±íÎÊÌâÂð£¿Ò»°ã³£¹æ·½·¨Ê¹ÓÃcase whenÓï¾äÀ´²úÉú¼ÆËãÁС£

---

SQL code:
--create table
create table #a
(
aid int identity(1,1) primary key,
bid int,
uname varchar(50),
cid int
)

create table #b
(
bid int identity(1,1) primary key,
bname varchar(50)
)

create table #c
(
cid int identity(1,1) primary key,
cname varchar(50)
)

--insert into
insert into #b(bname) values('b1')
insert into #b(bname) values('b2')
insert into #b(bname) values('b3')
insert into #b(bname) values('b4')

insert into #c(cname) values('²¿ÃÅa')
insert into #c(cname) values('²¿ÃÅb')
insert into #c(cname) values('²¿ÃÅc')
insert into #c(cname) values('²¿ÃÅd')

insert into #a(bid,uname,cid) values(1,'³õÖÐ',1