如何合并和排序列？ MySQL、CF8、MS Access 2003

发布于 2024-09-14 18:59:22 字数 374 浏览 5 评论 0原文

我想通过将两列名称（columnA 和 columnB）相加来生成按字母顺序排列的名称列表。

我已经尝试过：

<cfquery name="listAuthors" datasource="hhLibrary">
SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
FROM books
WHERE ColumnB LIKE '#firstletter#%'
ORDER BY ColumnB
</cfquery>

这是错误代码：参数太少。预期 1.

非常感谢任何帮助。

俄勒冈蜂蜜

原文

I want to produce an alphabetized list of names produced by adding together two columns of names, columnA, and columnB.

I have tried this:

<cfquery name="listAuthors" datasource="hhLibrary">
SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
FROM books
WHERE ColumnB LIKE '#firstletter#%'
ORDER BY ColumnB
</cfquery>

this is the error code:
Too few parameters. Expected 1.

any help greatly appreciated.

oregonHoney

分享到QQ

分享到微博

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

发布评论

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

泪眸﹌ 2024-09-21 18:59:22

更新：

select *
from (
    SELECT title, a1_Lname as Name
    FROM books  
    WHERE a1_Lname LIKE '#firstletter#%' 
    UNION ALL
    SELECT title, a2_Lname as Name
    FROM books  
    WHERE a2_Lname LIKE '#firstletter#%' 
) a
ORDER BY Name

Update:

select *
from (
    SELECT title, a1_Lname as Name
    FROM books  
    WHERE a1_Lname LIKE '#firstletter#%' 
    UNION ALL
    SELECT title, a2_Lname as Name
    FROM books  
    WHERE a2_Lname LIKE '#firstletter#%' 
) a
ORDER BY Name

回复收藏 0 原文

听不够的曲调 2024-09-21 18:59:22

在 Jet/ACE SQL 中，您不能在字段名称别名上放置 WHERE 子句或 ORDER BY - 您必须重复别名所引用的表达式。因此，将其替换为：

  SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
  FROM books
  WHERE ColumnB LIKE '#firstletter#%'
  ORDER BY ColumnB

... 为：

  SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
  FROM books
  WHERE a1_Lname + a2_Lname LIKE '#firstletter#%'
  ORDER BY a1_Lname + a2_Lname

如果您安装了 Access，我强烈建议您在 QBE 中的交互式 Access 中测试您的 SQL。如果您刚刚在 Access 中尝试过，您很快就会发现情况确实如此。

In Jet/ACE SQL you can't place a WHERE clause or ORDER BY on a field-name alias -- you have to repeat the expression the alias is referring to. So, replace this:

  SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
  FROM books
  WHERE ColumnB LIKE '#firstletter#%'
  ORDER BY ColumnB

...with this:

  SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
  FROM books
  WHERE a1_Lname + a2_Lname LIKE '#firstletter#%'
  ORDER BY a1_Lname + a2_Lname

If you have Access installed, I strongly encourage you to test your SQL in interactive Access, in the QBE. You would quickly have discovered that this is the case if you'd just tried it within Access.

回复收藏 0 原文

~没有更多了~