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

有没有一种方法可以将几个不同的查询(几乎相同)压缩为一个?

mysql

Jim · 技术社区 · 16 年前

除了WHERE子句中包含的内容外,我有大约4或5个完全相同的查询。例如,这些是按日期操作的报表,因此一个报表使用now()日期,其他报表使用按月和15天日期。所有字段完全相同。有没有一种方法可以将它们组合成一个查询,并动态地更改WHERE子句中的数据?或者其他解决方案?是吗?

谢谢

好的,伙计们,谢谢你们的回复。以下是实际查询。请注意,WHERE子句是唯一不同的东西,我真的不喜欢在需要添加新报表时编写几乎完全相同的另一个查询。

SELECT t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id
WHERE
t2.rpt_type = 'daily' AND
t2.tmstamp >= date_sub(curdate(), interval 1 month) AND
t2.tmstamp <= curdate() AND
t1.customer_id = ''



WHERE
t2.tmstamp >= '$date 00:00:00' AND
t2.tmstamp <= '$date 23:59:59' AND
t1.customer_id = '' AND
t2.deleted =  '0'


WHERE
t2.stamp >= date_sub(now(), interval 1 day) AND
t1.customer_id = '' AND
t2.band_type = '' AND
t2.deleted = '0'

3 回复 | 直到 16 年前

Samuel Carrijo 16 年前

看见 here

您有2个选项,构建动态SQL或使用coalesce选项。

我更喜欢后者(向下滚动一半链接到“合并函数”

尽管这两种选择都有其优缺点

erikkallen 16 年前

您可能需要创建一个视图。

Quassnoi 16 年前

使用 OR 或 UNION ALL :

SELECT  *
FROM    mytable
WHERE   date = NOW() OR date = NOW() - INTERVAL 1 DAY

SELECT  *
FROM    mytable
WHERE   date = NOW()
UNION ALL
SELECT  *
FROM    mytable
WHERE   date = NOW() - INTERVAL 1 DAY

更新:

如果需要三个结果集,则需要三个查询;如果有三个查询,则需要三个查询 SELECT 列表。

您可以在应用程序中组合查询(创建 选择 列出一次,只需添加一个 WHERE 条件)

或者,您可以在单个查询中获取所有内容,并在客户机上分析结果:

SELECT 1 AS resultset,
       t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id
WHERE
t2.rpt_type = 'daily' AND
t2.tmstamp >= date_sub(curdate(), interval 1 month) AND
t2.tmstamp <= curdate() AND
t1.customer_id = ''
UNION ALL
SELECT 2 AS resultset,
       t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id
WHERE
t2.tmstamp >= '$date 00:00:00' AND
t2.tmstamp <= '$date 23:59:59' AND
t1.customer_id = '' AND
t2.deleted =  '0'
SELECT 3 AS resultset,
       t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id
WHERE
t2.stamp >= date_sub(now(), interval 1 day) AND
t1.customer_id = '' AND
t2.band_type = '' AND
t2.deleted = '0'

这里,所有 3 结果集作为单个结果集返回,并带有一个附加字段( resultset )这样就可以区分它们。

当然,您也可以创建这样的视图:

CREATE VIEW v_customers AS
SELECT t1.customer_id, t1.name, t2.dtime, t6.band_type, t7.dop, t5.lun, t2.rpt
FROM
customer AS t1
Inner Join report AS t2 ON t1.customer_id = t2.customer_id
Inner Join employee AS t5 ON t5.employee_id = t2.employee_id
Inner Join band_type AS t6 ON t6.band_type_id = t2.band_type_id
Inner Join dop AS t7 ON t7.dop_id = t2.dop_id


SELECT *
FROM   v_customers
WHERE
t2.rpt_type = 'daily' AND
t2.tmstamp >= date_sub(curdate(), interval 1 month) AND
t2.tmstamp <= curdate() AND
t1.customer_id = ''

等等,但是您仍然需要复制一个选择列表。

P. S. 你到底想优化什么?现在的计算机在复制字符串方面非常好。