我必须执行以下SQL查询:
select answer_nbr, count(distinct user_nbr)
from tpoll_answer
where poll_nbr = 16
group by answer_nbr
LINQ to SQL查询
from a in tpoll_answer
where a.poll_nbr = 16 select a.answer_nbr, a.user_nbr distinct
映射到以下SQL查询:
select distinct answer_nbr, distinct user_nbr
from tpoll_answer
where poll_nbr = 16
到现在为止还好。然而,当尝试对结果进行分组时,问题就出现了,因为我找不到映射到我在这里编写的第一个查询的LINQ to SQL查询(感谢LINQPad让这个过程变得更容易)。下面是我找到的唯一一个能给我想要的结果的:
from answer in tpoll_answer where answer.poll_nbr = 16 _
group by a_id = answer.answer_nbr into votes = count(answer.user_nbr)
这反过来又在所有SQL查询中产生了丑陋和未优化的结果:
SELECT [t1].[answer_nbr] AS [a_id], (
SELECT COUNT(*)
FROM (
SELECT CONVERT(Bit,[t2].[user_nbr]) AS [value], [t2].[answer_nbr], [t2].[poll_nbr]
FROM [TPOLL_ANSWER] AS [t2]
) AS [t3]
WHERE ([t3].[value] = 1) AND ([t1].[answer_nbr] = [t3].[answer_nbr]) AND ([t3].[poll_nbr] = @p0)
) AS [votes]
FROM (
SELECT [t0].[answer_nbr]
FROM [TPOLL_ANSWER] AS [t0]
WHERE [t0].[poll_nbr] = @p0
GROUP BY [t0].[answer_nbr]
) AS [t1]
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [16]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
任何帮助都将不胜感激。
发布于 2009-01-16 09:42:41
没有对COUNT(DISTINCT {x}))
的直接支持,但你可以从IGrouping<,>
(即group by
返回的内容)中模拟它;我恐怕我只“做”C#,所以你必须转换成VB……
select new
{
Foo= grp.Key,
Bar= grp.Select(x => x.SomeField).Distinct().Count()
};
下面是一个Northwind示例:
using(var ctx = new DataClasses1DataContext())
{
ctx.Log = Console.Out; // log TSQL to console
var qry = from cust in ctx.Customers
where cust.CustomerID != ""
group cust by cust.Country
into grp
select new
{
Country = grp.Key,
Count = grp.Select(x => x.City).Distinct().Count()
};
foreach(var row in qry.OrderBy(x=>x.Country))
{
Console.WriteLine("{0}: {1}", row.Country, row.Count);
}
}
TSQL不是我们想要的,但它可以完成我们想要的工作:
SELECT [t1].[Country], (
SELECT COUNT(*)
FROM (
SELECT DISTINCT [t2].[City]
FROM [dbo].[Customers] AS [t2]
WHERE ((([t1].[Country] IS NULL) AND ([t2].[Country] IS NULL)) OR (([t1]
.[Country] IS NOT NULL) AND ([t2].[Country] IS NOT NULL) AND ([t1].[Country] = [
t2].[Country]))) AND ([t2].[CustomerID] <> @p0)
) AS [t3]
) AS [Count]
FROM (
SELECT [t0].[Country]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] <> @p0
GROUP BY [t0].[Country]
) AS [t1]
-- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1
但是,结果是正确的-可以通过手动运行它来验证:
const string sql = @"
SELECT c.Country, COUNT(DISTINCT c.City) AS [Count]
FROM Customers c
WHERE c.CustomerID != ''
GROUP BY c.Country
ORDER BY c.Country";
var qry2 = ctx.ExecuteQuery<QueryResult>(sql);
foreach(var row in qry2)
{
Console.WriteLine("{0}: {1}", row.Country, row.Count);
}
定义如下:
class QueryResult
{
public string Country { get; set; }
public int Count { get; set; }
}
发布于 2011-03-05 05:38:49
Marc Gravell引用的Northwind示例可以用group语句直接选择的City列重写:
from cust in ctx.Customers
where cust.CustomerID != ""
group cust.City /*here*/ by cust.Country
into grp
select new
{
Country = grp.Key,
Count = grp.Distinct().Count()
};
发布于 2009-01-16 08:31:05
Linq to sql不支持计数(Distinct ...)。因此,您必须将代码中的.NET方法映射到Sql server函数(因此Count(distinct..))并使用它。
顺便说一句,如果你以既不是VB.NET也不是C#的格式发布从工具包复制来的伪代码,这是没有帮助的。
https://stackoverflow.com/questions/448203
复制相似问题