SELECT (A.Count1/B.CountTotal) AS Ratio FROM (SELECT Count(*) AS Count1 FROM (SELECT ddfield FROM table1 UNION (SELECT ddfield FROM table2)) table WHERE table.ddfield='dd') A, (SELECT Count(*) CountTotal FROM (SELECT ddfield FROM table1 UNION (SELECT ddfield FROM table2))) B
问题表达的不是很清楚,你的意思是不是要统计这几个表里面值为dd的记录的总数占这几个表的记录总数的百分比? 如果是这个意思的话,hiflower的语句的整体思路是对头的,但是忽略了一个问题:因为你只select了一个字段来Union,而Union的结果集中,所有相同的记录都会合并为一条记录,所以你的Count1最大只可能为1,因此Union的结果集需要有关键字,但是各个表的关键字可能数据类型不一样甚至关键字的字段数量都不一样,所以不好统一。 我也没有想到什么好方法,只想到两个笨方法。笨方法之一:把每个表的dd的count和总记录数先都统计出来用变量保存起来,然后再(dd_count 的总和) / (所有表记录数的总和)。 笨方法二:是笨方法一的延伸,使用临时表,实现如下: create table #temp1( [ID] [int] IDENTITY (1, 1) NOT NULL, --临时表的自增字段,保证插入记录时相同记录不会合并 [dd_Field] [varchar] (50) )insert into #temp1(dd_Field) select dd_Field from table1insert into #temp1(dd_Field) select dd_Field from table2--有多少个表就在这里insert多少次select ((select count(ID) from #temp1 where dd_Field = 'dd') / (select count(ID) from #temp1)) as Ratiodrop table #temp1
这个应该可以了SELECT CAST(A.CountDD AS FLOAT) / B.CountTotal AS Ratio FROM (SELECT COUNT(*) AS CountDD FROM (SELECT id, ddfield FROM table1 UNION SELECT id, ddfield FROM table2) x WHERE x.ddfield = 'dd') A CROSS JOIN (SELECT COUNT(*) AS CountTotal FROM (SELECT id, ddfield FROM table1 UNION SELECT id, ddfield FROM table2) y) B
FROM
(SELECT Count(*) AS Count1
FROM (SELECT ddfield FROM table1 UNION (SELECT ddfield FROM table2)) table
WHERE table.ddfield='dd') A,
(SELECT Count(*) CountTotal
FROM (SELECT ddfield FROM table1 UNION (SELECT ddfield FROM table2))) B
如果是这个意思的话,hiflower的语句的整体思路是对头的,但是忽略了一个问题:因为你只select了一个字段来Union,而Union的结果集中,所有相同的记录都会合并为一条记录,所以你的Count1最大只可能为1,因此Union的结果集需要有关键字,但是各个表的关键字可能数据类型不一样甚至关键字的字段数量都不一样,所以不好统一。
我也没有想到什么好方法,只想到两个笨方法。笨方法之一:把每个表的dd的count和总记录数先都统计出来用变量保存起来,然后再(dd_count 的总和) / (所有表记录数的总和)。
笨方法二:是笨方法一的延伸,使用临时表,实现如下:
create table #temp1(
[ID] [int] IDENTITY (1, 1) NOT NULL, --临时表的自增字段,保证插入记录时相同记录不会合并
[dd_Field] [varchar] (50) )insert into #temp1(dd_Field)
select dd_Field
from table1insert into #temp1(dd_Field)
select dd_Field
from table2--有多少个表就在这里insert多少次select
((select count(ID) from #temp1 where dd_Field = 'dd') / (select count(ID) from #temp1)) as Ratiodrop table #temp1
FROM (SELECT COUNT(*) AS CountDD
FROM (SELECT id, ddfield
FROM table1
UNION
SELECT id, ddfield
FROM table2) x
WHERE x.ddfield = 'dd') A CROSS JOIN
(SELECT COUNT(*) AS CountTotal
FROM (SELECT id, ddfield
FROM table1
UNION
SELECT id, ddfield
FROM table2) y) B