我的语法代码有问题

Question

我有这个任务：A. 串联：生成报告，将供应商名称和位置列为"名称(位置("b. 上面的列表在两个字段中都包含尾随空格;修剪空格并显示结果。c. 为数据库中新派生的虚拟列指定名称d. 作为提醒，检索供应商"DLL01"提供的产品数量e. 检索每个供应商提供的产品数量，并在两列中显示结果"vend_id"和"num_products"我写了一些代码，但它没有运行。

a. select [vend_name] +'('+[vend_address] + [vend_city]+')' as 'Name (Location)'
from [dbo].[Vendors]
b. select 
Ltrim(Rtrim([vend_name] +'('+[vend_address] + [vend_city]+ ')' )as 'Name (Location)'
from [dbo].[Vendors]
c.select 
Ltrim(Rtrim([vend_name] +'('+[vend_address] + [vend_city]+')')),'   ','  '+ char(7)),
char(7),'') as 'vend information'
from [dbo].[Vendors]
d.select [vend_name] +'('+[vend_address] + [vend_city]+')' as 'Name (Location)'
from [dbo].[Vendors]
e. select [vend_id]='DLL01', count([vend_id]) as num products
from [dbo].[Vendors]
where [vend_id]='DLL01'

我认为这只是语法问题。

你可以帮我吗？

谢谢！！！

Answer 1

a. 看起来不错。在(之前添加了一个空格

select [vend_name] +' ('+[vend_address] + [vend_city]+')' as 'Name (Location)'
from [dbo].[Vendors]

b. 添加了缺失)

select Ltrim(Rtrim([vend_name] +' ('+[vend_address] + [vend_city]+ ')')) as 'Name (Location)'
from [dbo].[Vendors]

c. 避免列名中包含(空格(。

select Ltrim(Rtrim([vend_name] +' ('+[vend_address]+[vend_city]+')')) as 'vendor_information'
from [dbo].[Vendors]

d. 增加GROUP BY条款。

select [vend_name] +' ('+[vend_address] + [vend_city]+')' as 'Name (Location)'
from [dbo].[Vendors]
WHERE [vend_id]='DLL01'

e. 添加了*以获取计数并将计数更改为LTRIM(RTRIM(x))。

select [vend_id], count(*) as num_products
from [dbo].[Vendors]
GROUP BY [vend_id]

我不确定期望是什么，但您应该在所有列上使用CC_7，因为空格可能位于其中任何一列中。

Answer 2

对于 b，您需要关闭 Ltrim 括号 - 您只关闭了 rtrim 括号：

   select Ltrim(Rtrim([vend_name] +'('+[vend_address] + [vend_city]+ ')' ))as 'Name (Location)'
    from [dbo].[Vendors]