当需要执行大量结构相似但参数不同的查询时,可以使用以下几种SQL技术来合并这些查询,提高效率:
-- 原始多个查询:
-- SELECT * FROM users WHERE id = 1;
-- SELECT * FROM users WHERE id = 2;
-- SELECT * FROM users WHERE id = 3;
-- 合并为:
SELECT * FROM users WHERE id IN (1, 2, 3);
-- 统计不同状态的数量
SELECT
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_count,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM users;
-- 合并多个查询结果
SELECT 'category1' AS category, COUNT(*) FROM products WHERE category = 'category1'
UNION ALL
SELECT 'category2' AS category, COUNT(*) FROM products WHERE category = 'category2'
UNION ALL
SELECT 'category3' AS category, COUNT(*) FROM products WHERE category = 'category3';
WITH params AS (
SELECT 1 AS id UNION ALL
SELECT 2 UNION ALL
SELECT 3
)
SELECT u.*
FROM users u
JOIN params p ON u.id = p.id;
-- PostgreSQL示例
SELECT * FROM users WHERE id = ANY(ARRAY[1, 2, 3]);
-- MySQL 8.0+ JSON示例
SELECT * FROM users WHERE id MEMBER OF('[1, 2, 3]');
-- SQL Server PIVOT示例
SELECT *
FROM (
SELECT category, product_id
FROM products
) AS src
PIVOT (
COUNT(product_id)
FOR category IN ([category1], [category2], [category3])
) AS pvt;
这些技术可以显著减少数据库往返次数,提高应用程序性能。