根据另一个表从表中选择记录计数 [英] select record count from table based on two another table
问题描述
我有3张如下表格:
订单:order_id,order_date
司机:driver_id,driver_name
通知:driver_id,order_id,order_delivery
i想要来自表格通知的记录数
其中order_date> = startDate AND order_date< = endDate
示例1
startDate = 2014/01/01
endDate = 2014/01/31
结果------------------
< b> driverName1 123
dirverName2 427
driverName3 234
示例1
startDate = 2014/01/01
endDate = 2014/01/20
结果-------- ----------
driverName1 38
dirverName2 147
driverName3 94
非常感谢
这是您的结果希望这对您有所帮助。
- 创建表
创建 TABLE [dbo]。[orders](
[order_id] [ int ] NOT NULL ,
[order_date] DateTime NULL
)
CREATE TABLE [dbo]。[drivers](
[driver_id] [ int ] NOT < span class =code-keyword> NULL ,
[driver_name] varchar ( 50 ) NULL
)
CREATE 表 [dbo]。[通知](
[driver_id] [ int ] 不是跨度> NULL ,
[order_id] [ int ] NOT NULL ,
[order_delivery] varchar ( 50 ) NULL
)
GO
- 示例插入
INSERT [dbo]。[orders] VALUES ( 1 ,getdate())
INSERT [dbo]。[orders] VALUES ( 2 ,DATEADD(日, 30 ,getdate()))
INSERT [ dbo]。[orders] VALUES ( 3 ,DATEADD(day, 15 ,getdate()))
INSERT [dbo]。[drivers] VALUES ( 1 ,' Driver1')
INSERT [dbo]。[drivers] VALUES ( 2 ,' Driver2')
INSERT [ dbo]。[drivers] VALUES ( 3 ,' Driver3')
INSERT [dbo]。[drivers] VALUES ( 4 ,' Driver4')
INSERT [dbo]。[notifications] VALUES ( 1 , 1 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 1 , 1 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 1 , 2 ,' < span class =code-string> YES')
INSERT [dbo]。[notifications] VALUES ( 1 , 3 , ' YES')
INSERT [dbo]。[notifications] VALUES ( 1 , 4 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 2 , 1 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 2 , 2 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 2 , 2 ,' YES')
INSERT [dbo]。[通知] VALUES ( 2 , 2 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 2 , 3 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 3 , 2 ,' YES')
INSERT [dbo]。[notification] VALUES ( 3 , 2 ,' YES')
INSERT [ dbo]。[通知] VALUES (< span class =code-digit> 3 , 2 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 3 , 2 ,' YES')
INSERT [dbo]。[notifications] VALUES ( 3 , 3 ,' YES')
- -------------
- 您的选择查询
声明 @ StartDate datetime = getdate(),
@ endate datetime = DATEADD(day, 30 ,getdate())
选择 D.Driver_Name,
计数(N.order_delivery)TotalCounts
FROM
通知N
Left 外 JOIN 订单O
ON N.order_id = O.Order_id
左 外 JOIN drivers D
ON N.driver_id = D.driver_id
其中
O.order_date @ StartDate 和 @ endate
组 BY
D.Driver_Name
我想你想要下面的东西
选择
driverName,count(Order_id)
来自订单O
加入通知N 开 O.Order_ID = N.Order_Id
加入驱动程序D 开启 N.Driver_id = D.Driver_ID
其中 order_date> = startDate
AND order_date< = endDate
组 按 driverName
另请参阅联接
SQL Joins - W3Schools [ ^ ]
从订单中选择计数(N.order_id)O左连接通知N在O.order_id = N.order_id,其中O.order_date> = startDate AND order_date< = EndDate
i have 3 tables like below :
orders : order_id,order_date
drivers : driver_id, driver_name
notifications : driver_id , order_id,order_delivery
i want record count from table notifications
where order_date >= startDate AND order_date <= endDate
Example 1
startDate=2014/01/01
endDate= 2014/01/31
Result------------------
driverName1 123
dirverName2 427
driverName3 234
Example 1
startDate=2014/01/01
endDate= 2014/01/20
Result------------------
driverName1 38
dirverName2 147
driverName3 94
Thanks a lot解决方案Here is your result hope this will help you.
-- Create Table CREATE TABLE [dbo].[orders]( [order_id] [int] NOT NULL, [order_date] DateTime NULL ) CREATE TABLE [dbo].[drivers]( [driver_id] [int] NOT NULL, [driver_name] varchar(50) NULL ) CREATE TABLE [dbo].[notifications]( [driver_id] [int] NOT NULL, [order_id] [int] NOT NULL, [order_delivery] varchar(50) NULL ) GO -- Sample Insert INSERT [dbo].[orders] VALUES (1, getdate()) INSERT [dbo].[orders] VALUES (2, DATEADD(day,30,getdate()) ) INSERT [dbo].[orders] VALUES (3, DATEADD(day,15,getdate()) ) INSERT [dbo].[drivers] VALUES (1, 'Driver1') INSERT [dbo].[drivers] VALUES (2, 'Driver2') INSERT [dbo].[drivers] VALUES (3, 'Driver3') INSERT [dbo].[drivers] VALUES (4, 'Driver4') INSERT [dbo].[notifications] VALUES (1,1, 'YES') INSERT [dbo].[notifications] VALUES (1,1, 'YES') INSERT [dbo].[notifications] VALUES (1,2, 'YES') INSERT [dbo].[notifications] VALUES (1,3, 'YES') INSERT [dbo].[notifications] VALUES (1,4, 'YES') INSERT [dbo].[notifications] VALUES (2,1, 'YES') INSERT [dbo].[notifications] VALUES (2,2, 'YES') INSERT [dbo].[notifications] VALUES (2,2, 'YES') INSERT [dbo].[notifications] VALUES (2,2, 'YES') INSERT [dbo].[notifications] VALUES (2,3, 'YES') INSERT [dbo].[notifications] VALUES (3,2, 'YES') INSERT [dbo].[notifications] VALUES (3,2, 'YES') INSERT [dbo].[notifications] VALUES (3,2, 'YES') INSERT [dbo].[notifications] VALUES (3,2, 'YES') INSERT [dbo].[notifications] VALUES (3,3, 'YES') --------------- -- Your select Query Declare @StartDate datetime= getdate(), @endate datetime=DATEADD(day,30,getdate()) Select D.Driver_Name , Count(N.order_delivery) TotalCounts FROM notifications N Left Outer JOIN orders O ON N.order_id=O.Order_id Left Outer JOIN drivers D ON N.driver_id=D.driver_id Where O.order_date between @StartDate and @endate Group BY D.Driver_Name
I guess you want something like below
select driverName,count(Order_id) from orders O Join notifications N On O.Order_ID=N.Order_Id Join drivers D On N.Driver_id=D.Driver_ID where order_date >= startDate AND order_date <= endDate Group by driverName
Also refer for Joins
SQL Joins - W3Schools[^]
select count(N.order_id) from orders O left join notifications N on O.order_id=N.order_id where O.order_date >=startDate AND order_date <=EndDate
这篇关于根据另一个表从表中选择记录计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!