Subsonic:自连接查询需要

发布于 2024-09-10 14:43:14 字数 257 浏览 3 评论 0原文

我想构建将在.net 中使用的查询。下面你可以看到sql查询，任何人都可以给我等效的亚音速查询

选择不同的
  a2.AccountID AS BID，
  a2.AccountName AS 品牌
从
  账户一
  INNER JOIN 帐户 a2 ON a.ParentID = a2.AccountID
在哪里
  a.账户类型ID = 6
订购依据
  品牌

请帮我。

原文

I want to construct the query which is going to be used in .net. Below you can see the sql query, any one can give me the equivalent subsonic query

SELECT DISTINCT
  a2.AccountID AS BID,
  a2.AccountName AS Brand
FROM
  Account a
  INNER JOIN Account a2 ON a.ParentID = a2.AccountID
WHERE
  a.AccountTypeID = 6
ORDER BY
  Brand

Please help me.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

月依秋水 2024-09-17 14:43:14

亚音速 2 还是 3？

使用 SubSonic，你总是有一个漂亮的后门。

它在 2.x 中称为 InlineQuery，在 3.x 中称为 CodingHorror

，例如：

var result = DB.Query().ExecuteReader("SELECT DISTINCT
      a2.AccountID AS BID,
      a2.AccountName AS Brand
   FROM Account a
     INNER JOIN Account a2 ON a.ParentID = a2.AccountID
   WHERE a.AccountTypeID = ?accounttypeid
   ORDER BY Brand", 6);

如果由于语法检查和 sql 转换而想保留流畅的界面。这是我能想到的另一种方法（SubSonic 2.2），

        DataTable result = DB.Select(
                "a1." + Account.Columns.AccountId + " as BID",
                "a2." + Account.Columns.AccountName + " as Brand")
            .From(Account.Schema.QualifiedName + " a1")
            .InnerJoin(Account.Schema.QualifiedName + " a2", 
                      "a2." + Account.Columns.account_id,
                "a1", "a1." + Account.Columns.parent_id)
            .Where("a1." + Account.Columns.AccountTypeId).IsEqualTo(6)
            .OrderAsc("a2." + Account.Columns.AccountName)
            .ExecuteDataSet().Tables[0];

但我从未这样做过，也没有验证过。但也许它有效。

SubSonic 2 or 3?

With SubSonic you always have a nice backdoor.

It's called InlineQuery in 2.x and CodingHorror in 3.x

e.g:

var result = DB.Query().ExecuteReader("SELECT DISTINCT
      a2.AccountID AS BID,
      a2.AccountName AS Brand
   FROM Account a
     INNER JOIN Account a2 ON a.ParentID = a2.AccountID
   WHERE a.AccountTypeID = ?accounttypeid
   ORDER BY Brand", 6);

If you want to stay with the fluent interface because of the syntax checking and the sql conversion. Here is another approach I could think of (SubSonic 2.2)

        DataTable result = DB.Select(
                "a1." + Account.Columns.AccountId + " as BID",
                "a2." + Account.Columns.AccountName + " as Brand")
            .From(Account.Schema.QualifiedName + " a1")
            .InnerJoin(Account.Schema.QualifiedName + " a2", 
                      "a2." + Account.Columns.account_id,
                "a1", "a1." + Account.Columns.parent_id)
            .Where("a1." + Account.Columns.AccountTypeId).IsEqualTo(6)
            .OrderAsc("a2." + Account.Columns.AccountName)
            .ExecuteDataSet().Tables[0];

But I never did this and I haven't verified it. But maybe it works.

回复收藏 0 原文

~没有更多了~