sqlite查询最早的日期

debugcn 发表于 Dev

麦克风

我有以下查询：

  SELECT (CASE WHEN cc.id IS NULL THEN dc.id ELSE cc.id END) AS id,
 l.CALL,l.QSO_DATE,cc.prefix,l.state,l.QSL_RCVD,l.Band,l.Mode,
(CASE  WHEN CC.Country IS NULL THEN dc.Country ELSE cc.Country END) AS country,
(CASE  WHEN CC.Country IS NULL THEN 'Y' ELSE 'N' END) AS 'deleted',
(CASE  WHEN CC.flags IS NULL THEN dc.flags ELSE cc.flags END) AS 'flag'
FROM lotw l
LEFT OUTER JOIN CountryCode AS cc on cc.code = l.dxcc 
LEFT OUTER JOIN DeleteCountry AS dc on dc.code = l.dxcc 
WHERE l.DXCC > '' 

GROUP BY CC.Country,dc.Country  ORDER BY cc.COUNTRY,dc.COUNTRY.

它返回一组与国家/地区代码表代码字段匹配的唯一国家/地区。速度还不到一秒。

我需要扩展它，以便查询从LOTW表中选择最早的QSLRDATE。所以我像这样修改查询：

SELECT (CASE WHEN cc.id IS NULL THEN dc.id ELSE cc.id END) AS id, l.CALL,l.QSO_DATE,cc.prefix,l.state,l.QSL_RCVD,l.Band,l.Mode,
(CASE  WHEN CC.Country IS NULL THEN dc.Country ELSE cc.Country END) AS country,
(CASE  WHEN CC.Country IS NULL THEN 'Y' ELSE 'N' END) AS 'deleted',
(CASE  WHEN CC.flags IS NULL THEN dc.flags ELSE cc.flags END) AS 'flag'
FROM lotw l
LEFT OUTER JOIN CountryCode AS cc on cc.code = l.dxcc 
LEFT OUTER JOIN DeleteCountry AS dc on dc.code = l.dxcc 
WHERE l.QSLRDATE = (SELECT MIN(l2.QSLRDATE) FROM LOTW l2 WHERE l2.DXCC=l.DXCC) AND l.DXCC > '' 

GROUP BY CC.Country,dc.Country  ORDER BY cc.COUNTRY,dc.COUNTRY

这行得通，但性能从不到一秒缩短到了15秒。我添加的子查询：

l.QSLRDATE = (SELECT MIN(l2.QSLRDATE) FROM LOTW l2 WHERE l2.DXCC=l.DXCC)

不可能是实现我想要的最好的方法。任何帮助都会很棒。

MatBailie

尝试加入子查询，而不是在WHERE子句中使用相关子查询。

SELECT
  (CASE WHEN cc.id IS NULL THEN dc.id ELSE cc.id END) AS id,
  l.CALL,l.QSO_DATE,cc.prefix,l.state,l.QSL_RCVD,l.Band,l.Mode,
  (CASE  WHEN CC.Country IS NULL THEN dc.Country ELSE cc.Country END) AS country,
  (CASE  WHEN CC.Country IS NULL THEN 'Y' ELSE 'N' END) AS 'deleted',
  (CASE  WHEN CC.flags IS NULL THEN dc.flags ELSE cc.flags END) AS 'flag'
FROM
  (SELECT dxcc, MIN(qslrdate) AS qslrdate FROM lotw GROUP BY dxcc) AS qslr
     INNER JOIN lotw AS l ON l.dxcc = qslr.dxcc AND l.qslrdate = qslr.qslrdate
LEFT OUTER JOIN CountryCode AS cc on cc.code = l.dxcc 
LEFT OUTER JOIN DeleteCountry AS dc on dc.code = l.dxcc 
WHERE l.DXCC > ''
GROUP BY CC.Country,dc.Country  ORDER BY cc.COUNTRY,dc.COUNTRY

另外，请确保lotw已启用索引，(dxcc, qslrdate)以确保子查询和联接均运行良好。

实际上，请确保首先拥有该索引，然后重试查询。然后，如果性能仍然很差，请尝试上面的查询。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。