ÇóÒ»Ìõ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
Ïà¹ØÎÊ´ð£º
¿ÉÄÜÒòΪ¹¤×÷µÄÔÒò ½Ó´¥Êý¾Ý¿âÕâ¿é±È½ÏÉÙ£¬Ö®Ç°¶¼ÊÇ×ö³ÌÐòÕâ¿é£¬Êý¾Ý¿âÕâ¿é¶¼ÓÐרÃŵÄÈËÀ´×ö ·Ö¹¤¶¼ºÜÃ÷ϸ ËùÒÔ¶ÔÊý¾Ý¿âÕâÒ»¿éÍêÈ«²»Á˽⡣ǰ¶Îʱ¼ä È¥ÃæÊÔÁ˼¸¼Ò¹«Ë¾ ¼¸ºõ¶¼ÊÇÔÚÊý¾Ý¿âÕâ¿é¹ÒµôµÄ Á¬¸ö¼òµ¥µÄSQ ......
×Ö¶Î1,×Ö¶Î2.....×Ö¶ÎN,Status,ParentID
1,Name1....test1,1,99
1,Name1....test1,3,99
1,Name2....test2,1,101
1,Name2....test2,3,101
1,Name3....test3,2,101
1,Name1....test1,4,101
ÏëÒªµÄ½á¹ûÊÇ:
1,Na ......
±íÊý¾Ý
COL1 COL2 COL2 COL4 COL5
----------------------------------------------------------------------------------------------
2010-05-05 00:00 ......
¸÷λºÃ£¬ÎÒ¸Õ°ÑÒ»¸öASPÍøÕ¾ÉèÖúÃÁË£¬Ò²ÄÜÕý³£·ÃÎÊ¡£µ«ÊÇÔÚÎÒ±¾»úÉϲ»ÄÜ´ò¿ªadmin\login.asp,ËùÒÔÎÞ·¨ÊµÏÖºǫ́¹ÜÀí¡££¨´íÎóÌáʾÎÞ·¨ÕÒµ½ÍøÒ³£¬´úÂë404£¬ÎҵIJÙ×÷ϵͳÊÇwin2000 server)
µ«ÊÇÆæ¹ÖµÄÊÇÔÚ¾ÖÓòÍøµÄÆäËû» ......