代码之家 › 专栏 › 技术社区 › Satish

SQL中涉及两个表的联接的查询

mysql sql

Satish · 技术社区 · 16 年前

编辑2

我有两张桌子 reports 和 holidays .

报告 : (username varchar(30),activity varchar(30),hours int(3),report_date date)

假期 : (holiday_name varchar(30), holiday_date date)

select * from reports 给予

+----------+-----------+---------+------------+  
| username |  activity |  hours  |   date     |
+----------+-----------+---------+------------+  
| prasoon  |   testing |    3    | 2009-01-01 |
| prasoon  |   coding  |    4    | 2009-01-03 |
| gautam   |   coding  |    1    | 2009-01-04 |  
| prasoon  |   coding  |    4    | 2009-01-06 |
| prasoon  |   coding  |    4    | 2009-01-10 |
| gautam   |   coding  |    4    | 2009-01-10 |
+----------+-----------+---------+------------+

select * from holidays 给予

+--------------+---------------+  
| holiday_name |  holiday_date |
+--------------+---------------+ 
| Diwali       |   2009-01-02  |
| Holi         |   2009-01-05  |  
+--------------+---------------+

编辑

当我使用以下查询时

 SELECT dates.date AS date,
  CASE 
    WHEN holiday_name IS NULL THEN COALESCE(reports.activity, 'Absent') 
    WHEN holiday_name IS NOT NULL and reports.activity IS NOT NULL THEN  reports.activity
  ELSE ''
    END 
  AS activity,
  CASE WHEN holiday_name IS NULL THEN COALESCE(reports.hours, 'Absent')
    WHEN holiday_name IS NOT NULL and reports.hours IS NOT NULL THEN reports.hours
    ELSE ''
    END 
  AS hours,
  CASE 
    WHEN holiday_name IS NULL THEN COALESCE(holidays.holiday_name, '')
    ELSE holidays.holiday_name
    END 
  AS holiday_name
  FROM dates 
  LEFT OUTER JOIN reports ON dates.date = reports.date 
  LEFT OUTER JOIN holidays ON dates.date = holidays.holiday_date
  where reports.username='gautam' and dates.date>='2009-01-01' and dates.date<='2009-01-09';

我得到了以下输出

   +----------+-----------+---------+------------+  
   |  date    |  activity |  hours  |   holiday  |
   +----------+-----------+---------+------------+  
   |2009-01-04|   coding  |    1    |            |
   +----------+-----------+---------+------------+

但我预料到了

   +----------+-----------+---------+------------+  
   |  date    |  activity |  hours  |   holiday  |
   +----------+-----------+---------+------------+  
   |2009-01-01|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-02|           |         | Diwali     |
   +----------+-----------+---------+------------+
   |2009-01-03|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-04|  Coding   |   1     |            |
   +----------+-----------+---------+------------+
   |2009-01-05|           |         | Holi       |
   +----------+-----------+---------+------------+
   |2009-01-06|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-07|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-08|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+
   |2009-01-09|  Absent   | Absent  |            |
   +----------+-----------+---------+------------+

如何修改上述查询以获得所需的输出(对于特定用户(本例中为Gautam))?

3 回复 | 直到 16 年前

colinmarc 16 年前

更新2: 这是你新问题的答案。请注意,您使用的case语句不正确- COALESCE(reports.activity, 'Absent') 将返回 reports.activity 如果它不是空的,并且 'Absent' 如果是。

首先你需要一张桌子 dates 您要检查的日期如下:

CREATE TABLE dates (date date);

然后手动填写:

date      
----------
2009-01-01
2009-01-02
2009-01-03
2009-01-04
2009-01-05
2009-01-06
2009-01-07
2009-01-08
2009-01-09
2009-01-10

这可以按程序创建,但这完全是另一个主题。

下面是使用左外部联接和嵌套选择的查询:

SELECT dates.date AS date,
CASE WHEN holiday_name IS NULL THEN COALESCE(user_reports.activity, 'Absent')
 ELSE '' END AS activity,
CASE WHEN holiday_name IS NULL THEN COALESCE(user_reports.hours, 'Absent')
 ELSE '' END AS hours,
COALESCE(holidays.holiday_name, '') AS holiday_name
FROM dates
LEFT OUTER JOIN 
 (SELECT * FROM reports WHERE reports.username='guatam') AS user_reports
 ON dates.date = user_reports.report_date 
LEFT OUTER JOIN holidays ON dates.date = holidays.holiday_date
WHERE dates.date>='2009-01-01' and dates.date<='2009-01-09';

回报:

date        activity    hours       holiday_name
----------  ----------  ----------  ------------
2009-01-01  Absent      Absent                  
2009-01-02                          Diwali      
2009-01-03  Absent      Absent                  
2009-01-04  coding      1                       
2009-01-05                          Holi        
2009-01-06  Absent      Absent                  
2009-01-07  Absent      Absent                  
2009-01-08  Absent      Absent                  
2009-01-09  Absent      Absent

但真正的问题是,你做的事情实践得很差- 您正在使用SQL格式化信息。这太糟糕了! 您应该只使用它来检索您的信息,然后用HTML或您要将数据拉入的任何内容来格式化它。您的选择应该是简单的:

SELECT * FROM reports WHERE username='guatam'
 AND date>='2009-01-01' AND date<='2009-01-9'

如果您需要的话,还可以为节假日提供一个单独的:

SELECT * from holidays

然后根据需要使用这些信息。

VeeArr 16 年前

我不知道你说的是什么意思:

填写缺失的日期…用“on” 离开“

您可以通过执行一对 outer joins 填充了所需日期范围内的日期列表的表。

El Yobo 16 年前

这是上面Colinmarc答案的一个扩展,只是为了展示如何避免在度假时“缺席”。否则,答案和他的差不多。

SELECT
    d.adate AS `date`,
    CASE WHEN holiday_name IS NULL THEN coalesce(activity, 'Absent') 
    ELSE '' END AS activity,
    CASE WHEN holiday_name IS NULL THEN coalesce(hours, 'Absent') 
    ELSE '' END AS activity,
    coalesce(holiday_name, '')
FROM (
    SELECT holiday_date AS adate FROM holidays
    UNION
    SELECT report_date AS adate FROM reports
) d
LEFT JOIN reports r ON (d.adate = r.report_date)
LEFT JOIN holidays h ON (d.adate = h.holiday_date)
ORDER BY adate ASC