主页 / dba / 问题 / 19769
Accepted
NReilingh
Asked: 2012-06-23 14:11:15 +0800 CST

如何避免在一系列嵌套的 SELECT 语句中重复信息？

  • 772

我正在研究一个 SQL 查询，它根据过去的购买（行业是场地票务）将客户历史记录放在一起。该报告将很大，大约有 80-90 列，输出 CSV 中每个事件或每个记录的事件类别都有一列。

数据库结构要求我使用嵌套的选择语句将必要的数据放入每条记录的列中——每列包含该类型事件的门票数量。我知道理论上如何做到这一点，但每个嵌入的选择语句都非常大，以至于完整的报告会超过我的数据库界面上 8,000 个字符的限制。这些语句之一如下所示：

(select count (*)
FROM
guest ig, event2 e, eventseat es, "order" o
WHERE ig.guestid = g.guestid
and ig.guestid = o.guestid
and o.orderid = es.orderid
and e.eventid = es.eventid
and e.incometype = 'T'
and e.eventtype in ('SS', 'BMF')
and es.status in ('2','4')
and es.price <> '0.00'
and es.price <> '5.00'
and e.year = '2010'       <-- THESE LINES ARE THE ONLY ONES THAT 
and e.run in ('SS-DANCE') <-- CHANGE PER SELECT STATEMENT 
) as 'SS-Dance',

这大约是 400 个字符，这只为单个查询中 <20 次迭代留下足够的空间，而不会超过字符限制。

有没有办法减少或概括它，这样我就不需要在每个 SELECT 的重复信息上浪费所有这些字符？

我在以下链接中整理了一个示例，其中包含我的数据库结构的简化版本和一些示例数据。目标是在每个嵌套 SELECT 不使用那么多字符的情况下获得相同的结果数据。

http://sqlfiddle.com/#!3/fc316/2

sql-server sql-server-2005

1 个回答

  1. Best Answer
    ;WITH x AS 
(
  SELECT g.guestid, e.[year], e.run, c = COUNT(*)
  FROM dbo.guest AS g
  INNER JOIN dbo.[order] AS o
  ON g.guestid = o.guestid
  INNER JOIN dbo.eventseat AS es
  ON o.orderid = es.orderid
  INNER JOIN dbo.event2 AS e
  ON e.eventid = es.eventid
  WHERE e.incometype = 'T'
  AND e.eventtype in ('SS', 'BMF')
  AND es.status in ('2','4')
  AND es.price NOT IN ( '0.00', '5.00')
  AND e.[year] = '2010'
  AND e.run IN ('SS-DANCE', 'SS-FILM', 'SS-OPERA')
  GROUP BY g.guestid, e.[Year], e.run
),
y AS 
(
  SELECT * FROM x PIVOT (MAX(c) FOR run IN 
  ([SS-DANCE],[SS-FILM],[SS-OPERA])) AS z
)
SELECT y.guestid, y.[year], g.[first], g.[last], 
  [SS-DANCE] = COALESCE(y.[SS-DANCE], 0), 
  [SS-FILM]  = COALESCE(y.[SS-FILM], 0), 
  [SS-OPERA] = COALESCE(y.[SS-OPERA], 0)
FROM y 
INNER JOIN dbo.guest AS g
ON y.guestid = g.guestid;

    我不确定我的 where 子句是否会像所写的那样为您工作。目前假设您只关心这三种类型（舞蹈、电影和歌剧）并且您只关心 2010 年。如果您需要跨年，您可以删除该WHERE条款。如果您只关心 2010 年的舞蹈和任何一年的电影，您将需要编写一组更复杂的 where 从句。

    一些评论：

    • 尽量避免保留字，如order,和。这些只会使事情复杂化，因为它们需要用方括号括起来（为了可读性和明确性，方括号比 mostly 更可取）。yearfirstlast"double quotes"
    • 始终使用dbo.（或任何schema合适的前缀）。
    • 正如我上面所建议的，坚持使用显式INNER JOIN语法。FROM x, y, z是给鸟的。
    • 6

相关问题