如何将某些列与常用的SQL片段语句结合使用 [英] How to get certain columns in combination with a commonly used SQL snippet statement

问题描述

所以，在这个特定时间，我找不到在我一直使用的语句中包含某些列名的方法。



以下是代码：

 SELECT 
 [TableName] = so.name，
 [RowCount] = MAX（si.rows），
 [Columnname] = c.name 
 FROM 
 sysobjects so，
 sysindexes si，
 syscolumns c 
 WHERE 
 so.xtype ='U'
 AND 
 si.id = OBJECT_ID（so.name）
 GROUP BY 
 so.name 
订购
 2 DESC 

这是没有syscloumns的原始代码：

 SELECT 
 [TableName] = so.name，
 [RowCount] = MAX（si.rows）
 FROM 
 sysobjects所以，
 sysindexes si 
 WHERE 
 so.xtype ='U'
 AND 
 si.id = OBJECT_ID（so.name）
 GROUP BY 
 so.name 
 ORDER BY 
 2 desc 

我认为我可以使用像C这样的东西.column，但它一直给我这个错误：

列'sysobjects.name'在选择列表中无效它不包含在聚合函数或GROUP BY子句中。

我所要做的就是获取每行数表格和某些栏目，如就业日期和销售的产品。但我似乎无法使用我已经拥有的那些列。



我尝试了什么：



试图在列表中添加一个syscolumns但是如上所述我一直收到错误。

解决方案

在SQL中，当您使用AGGREGATE函数时，SELECT列表中的所有列必须是此类函数的产品或包含在GROUP BY部分...

c.name 既没有...

这是开始...另一个问题是你的旧语法JOIN（这将创建一个非常昂贵的CROSS JOIN）......因为有在 syscolumns 上没有任何条件它将多个结果集并将其增长到一个极端大小...

您还通过转换对象名称来浪费资源对象id - OBJECT_ID（...） - 虽然你已经拥有了来自 sysobjects 的id ... ...

最后，它是不清楚第二个查询的目标是什么？

So, at this particular time, I cannot find a way to include certain column names in a statement that I have been using.

Here is the code:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows), 
	[Columnname] = c.name
FROM 
    sysobjects so, 
    sysindexes si, 
    syscolumns c
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC

this is the original code without the syscloumns:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows)
FROM 
    sysobjects so, 
    sysindexes si	
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
  2 desc

I figure that I could use something like C.column, but it keeps giving me this error:

Column 'sysobjects.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

All I am trying to do is get row count per table and have certain columns like date of employment and products sold. but I just can't seem to use what I already have to include those columns.

What I have tried:

tried to add a syscolumns into the list but as stated above I keep getting an error.

解决方案

In SQL, the moment you are using an AGGREGATE function all columns in the SELECT list must be a product of such function or be included in the GROUP BY part...
c.name does neither...
That's for beginning... The other problem is your old syntax of JOIN (which will create a very expensive CROSS JOIN)... As there is no condition whatsoever on syscolumns it will multiple the result sets and grow it to an extreme size...
You also wasting resource by converting object name to object id - OBJECT_ID(...) - while you already have the id at hand from sysobjects...
And finally, it is unclear what is your goal with the second query?

这篇关于如何将某些列与常用的SQL片段语句结合使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！