select 客户ID,count(*),sum(数量) from ( select a.*,b.客户ID from orderDetails a left join orders b on a.订单ID=b.订单ID ) aa group by 客户ID
SQL语句有问题 提示 服务器: 消息 409,级别 16,状态 2,行 1 sum or average aggregate 运算不能以 char 数据类型作为参数。
上面的数量改为case(数量 as int)
上面的数量改为cast(数量 as int)
select 客户ID,count(*),sum(cast(数量 as int)) from ( select a.*,b.客户ID from orderDetails a left join orders b on a.订单ID=b.订单ID ) aa group by 客户ID 试试这个,其实你”数量“这一列就应该是数值型的,但你表定义时用的char类型,所以用sum函数时就要进行转换
select 客户ID,count(*),sum(cast(数量 as int)) from ( select a.*,b.客户ID from orderDetails a left join orders b on a.订单ID=b.订单ID ) aa group by 客户ID
有点晕,不是说要产品种类个数吗?select 客户ID,count(distinct 类别ID) from OrderDetails a left join Orders b on a.订单ID=b.订单ID left join Products c on b.产品ID=c.产品ID group by 客户ID
SELECT A.[客户ID],COUNT(DISTINCT [类别ID] )AS [种类个数] FROM Orders A INNER JOIN OrderDetails B ON A.[订单ID] = B.[订单ID] INNER JOIN Products C ON B.[产品ID] = C.[产品ID] GROUP BY A.[客户ID]
select 客户ID,count(*),sum(数量) from
(
select a.*,b.客户ID from orderDetails a
left join orders b on a.订单ID=b.订单ID
) aa group by 客户ID
提示
服务器: 消息 409,级别 16,状态 2,行 1
sum or average aggregate 运算不能以 char 数据类型作为参数。
(
select a.*,b.客户ID from orderDetails a
left join orders b on a.订单ID=b.订单ID
) aa group by 客户ID
试试这个,其实你”数量“这一列就应该是数值型的,但你表定义时用的char类型,所以用sum函数时就要进行转换
(
select a.*,b.客户ID from orderDetails a
left join orders b on a.订单ID=b.订单ID
) aa group by 客户ID
left join Orders b on a.订单ID=b.订单ID
left join Products c on b.产品ID=c.产品ID
group by 客户ID
SELECT A.[客户ID],COUNT(DISTINCT [类别ID] )AS [种类个数] FROM Orders A
INNER JOIN OrderDetails B ON A.[订单ID] = B.[订单ID]
INNER JOIN Products C ON B.[产品ID] = C.[产品ID]
GROUP BY A.[客户ID]