代码之家 › 专栏 › 技术社区 › Andy Evans

统计记录时出现SQL联合问题

union sql

Andy Evans · 技术社区 · 15 年前

我有以下数据库模式(更改名称以保护无辜者)

Code Table

| code_id | code_desc |
|---------|-----------|
| 1       | good      |
|---------|-----------|
| 2       | bad       |
|---------|-----------|
| 3       | ugly      |
|---------|-----------|

Foo Table                               AssignedFoo Table

| foo_id | foo_desc | foo_dt    |       | assn_id | foo_id | code_id |
|--------|----------|-----------|       |---------|--------|---------|
| 101    | red      | 1/1/2000  |       | 301     | 101    | 1       |
|--------|----------|-----------|       |---------|--------|---------|
| 102    | green    | 6/1/2000  |       | 302     | 102    | 2       |
|--------|----------|-----------|       |---------|--------|---------|
| 102    | blue     | 12/1/2000 |
|--------|----------|-----------|

Bar Table                               AssignedBar Table

| bar_id | bar_desc | bar_dt    |       | assn_id | foo_id | code_id |
|--------|----------|-----------|       |---------|--------|---------|
| 201    | gold     | 12/1/2000 |       | 401     | 201    | 1       |
|--------|----------|-----------|       |---------|--------|---------|
| 202    | silver   | 1/1/2001  |       | 402     | 202    | 3       |
|--------|----------|-----------|       |---------|--------|---------|
| 202    | bronze   | 6/1/2001  |
|--------|----------|-----------|

我想计算映射到代码表的记录数,不管它们是foo还是bar。我认为下面的查询是可行的。

SELECT
    COUNT(c.code_id) AS coded,
    c.code_desc
FROM 
    code c
    INNER JOIN assignedfoo af ON af.code_id = c.code_id
    INNER JOIN foo f ON f.foo_id = af.foo_id
WHERE
    f.foo_dt >= [start date] AND 
    f.foo_dt <= [end date]
GROUP BY 
    c.code_desc

UNION ALL   

SELECT
    COUNT(c.code_id) AS coded,
    c.code_desc
FROM 
    code c
    INNER JOIN assignedbar ab ON ab.code_id = c.code_id
    INNER JOIN bar b ON b.bar_id = ab.bar_id
WHERE
    b.bar_dt >= [start date] AND 
    b.bar_dt <= [end date]
GROUP BY 
    c.code_desc

不幸的是,我得到了以下结果集

| coded | code_desc |
|-------|-----------|
| 1     | good      |
|-------|-----------|
| 1     | good      |
|-------|-----------|
| 1     | bad       |
|-------|-----------|
| 1     | ugly      |
|-------|-----------|

我想要的是这个

| coded | code_desc |
|-------|-----------|
| 2     | good      |
|-------|-----------|
| 1     | bad       |
|-------|-----------|
| 1     | ugly      |
|-------|-----------|

关于如何解决这个问题有什么建议吗?

任何帮助都将不胜感激。

谢谢!

4 回复 | 直到 15 年前

Joe Stefanelli 15 年前

select count(a.code_id) as Coded, a.code_desc
from (
SELECT
    c.code_id,
    c.code_desc
FROM 
    code c
    INNER JOIN assignedfoo af ON af.code_id = c.code_id
    INNER JOIN foo f ON f.foo_id = af.foo_id
WHERE
    f.foo_dt >= [start date] AND 
    f.foo_dt <= [end date]

UNION ALL   

SELECT
    c.code_id,
    c.code_desc
FROM 
    code c
    INNER JOIN assignedbar ab ON ab.code_id = c.code_id
    INNER JOIN bar b ON b.bar_id = ab.bar_id
WHERE
    b.bar_dt >= [start date] AND 
    b.bar_dt <= [end date]
) a
group by a.code_desc

Thakur 15 年前

Select COUNT(UnionTable.coded),UnionTable,code_description from 

(SELECT
    COUNT(c.code_id) AS coded,
    c.code_desc code_description
FROM 
    code c
    INNER JOIN assignedfoo af ON af.code_id = c.code_id
    INNER JOIN foo f ON f.foo_id = af.foo_id
WHERE
    f.foo_dt >= [start date] AND 
    f.foo_dt <= [end date]
GROUP BY 
    c.code_desc

UNION ALL   

SELECT
    COUNT(c.code_id) AS coded,
    c.code_desc code_description
FROM 
    code c
    INNER JOIN assignedbar ab ON ab.code_id = c.code_id
    INNER JOIN bar b ON b.bar_id = ab.bar_id
WHERE
    b.bar_dt >= [start date] AND 
    b.bar_dt <= [end date]
GROUP BY 
    c.code_desc ) UnionTable

   group by UnionTable.code_description

Guffa 15 年前

SELECT
    COUNT(*) AS coded,
    x.code_desc
FROM (

  SELECT
    c.code_desc, f.foo_dt
  FROM 
    code c
    INNER JOIN assignedfoo af ON af.code_id = c.code_id
    INNER JOIN foo f ON f.foo_id = af.foo_id

  UNION ALL   

  SELECT
    c.code_desc, f.foo_dt
  FROM 
    code c
    INNER JOIN assignedbar ab ON ab.code_id = c.code_id
    INNER JOIN bar b ON b.bar_id = ab.bar_id

) x
WHERE
  x.foo_dt >= [start date] AND 
  x.foo_dt <= [end date]
GROUP BY 
  x.code_desc

Jeff Wight 15 年前

SELECT c.code_desc, s.COUNT(NUM)
FROM
Code c JOIN
(
    SELECT code_id, COUNT(*) AS NUM
      FROM AssignedFoo af
      JOIN Foo f on af.foo_id = f.foo_id
      WHERE f.foo_dt >= [start date]
        AND f.foo_dt <= [end date]
    GROUP BY CODE_ID
    UNION ALL
    SELECT CODE_ID, COUNT(*) AS NUM
      FROM AssignedBar ab
      JOIN Bar b on ab.bar_id = b.bar_id
      WHERE b.bar_dt >= [start date]
        AND b.bar_dt <= [end date]
    GROUP BY code_id
) s ON c.code_id = s.code_id
GROUP BY c.code_id