我有一个客户数据库,我想运行一个查询来查找一天中使用多次的条形码,并只输出条形码。。。例如,如果条形码#1在特定日期被扫描了3次,它将在数据库中显示3笔交易,所有交易都带有条形码#1…这是我现在按日期搜索的一个基本查询。。。
Select *
FROM Customers
WHERE (dtCreated BETWEEN CONVERT(DATETIME, '2012-12-10 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-11 00:00:00', 102)) AND (sBarcode = '1')
我不想搜索条形码1,而是想知道2012年10月12日多次扫描的任何条形码(条形码)。
选择条形码,从table_name中计数(条形码)为cnt,其中date=desired_date group by barcode HAVING(count(*)>1)
只删除最后一个AND语句?
编辑:好的。你必须按条形码分组,每天都同意。仅此而已。类似于:
SELECT *
WHERE (dtCreated BETWEEN CONVERT(DATETIME, '2012-12-10 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-11 00:00:00', 102))
GROUP BY dtID, dtCreated
HAVING COUNT(*) > 1
Select Count(barcode) counter
FROM Customers
WHERE (dtCreated BETWEEN CONVERT(DATETIME, '2012-12-10 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-11 00:00:00', 102)) AND (sBarcode = '1')
group by barcode
having (COUNT(barcode) >= 1)
我会这样做:
select sbarcode
from customers
where cast(dtCreated as date) = '2012-12-10'
group by sbarcode
having count(*) > 1
如果您想要计数,那么您可以在select
行中包含, count(*)
。