多表组合排序

[网客Q]

表一、
ID,Uid,Title
1 ,1 ,abcd
2 ,2 ,abcd
3 ,3 ,abcd
4 ,1 ,abce

表二、
ID,Uid,Title
1 ,1 ,abcd
2 ,1 ,abcd
3 ,3 ,abcd
4 ,1 ,abcd
如何可以将Uid按以下方式排成
先统计总数 Uid1:5条 Uid2:1条 Uid3:2条

按Uid排成1,3,2列表（也就是按记录总数最多的显示在前）

[md5e]

先建试图

Create View V1 as
SELECT id,Uid,Title FROM table1
UNION
SELECT id,Uid,Title FROM table2

Select v1.*,(Select Count(Uid) From v1 Group by Uid) as Scoun From v1 ORder by Scount DESC

[网客Q]

但系我要用系ASP中怎样写呢

[md5e]

Create View V1 as
SELECT id,Uid,Title FROM table1
UNION
SELECT id,Uid,Title FROM table2

这个是用SQL查询分析器写


sql="Select v1.*,(Select Count(Uid) From v1 Group by Uid) as Scoun From v1 ORder by Scount DESC"
Conn.Execute(sql)

[网客Q]

不行啊，有个错误

[md5e]

Create View V1 as
SELECT id,Uid,Title FROM table1
UNION
SELECT id,Uid,Title FROM table2

这个是必须用SQL查询分析器写

[网客Q]

已经写了，也生成了一个V1的视图了

[网客Q]

CREATE VIEW v1 AS SELECT id, Userid

FROM T1 A INNER JOIN UserDBs B ON B.ID = A.UserID WHERE B.usertype = 3

UNION
SELECT id, Userid
FROM T2 C INNER JOIN UserDBs D ON D .ID = C.UserID WHERE D .usertype = 3


怎么出现列名ID不明确呢，要怎么改

[一把编程的菜刀]

select count(a.uid) as auid,count(b.uid) as buid from a,b where a.uid=b.uid
你先看看这个得到的结果是什么

[网客Q]

错误，能加我QQ赐教吗，106318982

[一把编程的菜刀]

如果用视图的话应该这么用

Create View V1 as 
SELECT id,Uid,Title FROM a  
UNION all
SELECT id,Uid,Title FROM b 

[一把编程的菜刀]

然后使用
sql="select count_big(uid) from v1 group by uid"
这样就可以了

[一把编程的菜刀]

[Quote=引用 10 楼 chinmo 的回复:]
如果用视图的话应该这么用

SQL codeCreate View V1 as
SELECT id,Uid,Title FROM a
UNION all
SELECT id,Uid,Title FROM b
[/Quote]
这个一定要用UNION all不然就会缺少数据