mysql查询姓氏的_mysql-WordPress SQL查询产品X的用户名和姓氏？

旧城等待， 2023-01-12 07:48 86阅读 0赞

我正在创建一个SQL查询,其中要显示从woocommerce商店购买了可下载产品的客户的全名和电子邮件.我正在寻找这样的东西：

user\_id first\_name last\_name user\_email order

\---------------------------------------------------------

1 Peter Jones a12@gmail Doc\_1

在上表中,Doc\_1代表产品的名称,在这种情况下,user\_id = 1的客户可以下载Doc\_1.对于wordpress用户,您知道表wp\_usermeta包含所有用户信息,如下所示：

umeta\_id user\_id meta\_key meta\_value

\---------------------------------------------

1 1 nickname petjon123

2 1 first\_name Peter

3 1 last\_name Jones

... ... ... ...

该列表下降了,包含更多的meta\_value \[s\],包括电子邮件等.如果meta\_key \[s\]是表标头,彼此显示first\_name和last\_name而不是放在顶部,则一切都会变得更加容易.到目前为止,我的查询中显示的是first\_name,但是我在获取last\_name时遇到了麻烦.我的SQL查询如下所示：

SELECT

wp\_usermeta.user\_id,

wp\_usermeta.meta\_value AS 'first\_name',

Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_email AS 'email',

Wp\_woocommerce\_order\_items\_1.order\_item\_name AS 'order'

FROM Wp\_woocommerce\_order\_items

INNER JOIN Wp\_woocommerce\_downloadable\_product\_permissions

ON Wp\_woocommerce\_order\_items.order\_id = Wp\_woocommerce\_downloadable\_product\_permissions.order\_id,

(Wp\_woocommerce\_order\_items AS Wp\_woocommerce\_order\_items\_1

INNER JOIN Wp\_woocommerce\_downloadable\_product\_permissions AS Wp\_woocommerce\_downloadable\_product\_permissions\_1

ON Wp\_woocommerce\_order\_items\_1.order\_id = Wp\_woocommerce\_downloadable\_product\_permissions\_1.order\_id)

INNER JOIN wp\_usermeta

ON Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_id = wp\_usermeta.user\_id

GROUP BY wp\_usermeta.user\_id,

wp\_usermeta.meta\_value,

Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_email,

Wp\_woocommerce\_order\_items\_1.order\_item\_name,

Wp\_woocommerce\_order\_items\_1.order\_item\_type,

wp\_usermeta.meta\_key

HAVING (((Wp\_woocommerce\_order\_items\_1.order\_item\_type) = 'line\_item')

AND ((wp\_usermeta.meta\_key) = 'first\_name'));

给我这个结果：

user\_id first\_name user\_email order

\--------------------------------------------

1 Peter a12@gmail Doc\_1

我手动检查了这些值,它们是正确的,但是,我必须说,如果不对结果进行分组,则会得到1000倍的结果,因此对数据进行分组可以通过基本上删除明显过多的重复项来简化结果.我怀疑该查询是否有效,因为直到结果出现为止的执行时间是/-20368.14 ms,因此对于改进此代码的任何建议都非常欢迎.如此说来,我以为我必须做另一个查询才能获得last\_name,从而使它成为一个嵌套查询.如果我在MS Access中打开表,请创建一个查询Query1(上面的查询),然后将Query1与该表连接起来,以获得所需的结果.用于此的SQL代码如下：

SELECT

Query1.user\_id,

Query1.first\_name,

wp\_usermeta.meta\_value AS 'last\_name',

Query1.user\_email,

Query1.order\_item\_name

FROM wp\_usermeta

INNER JOIN Query1

ON wp\_usermeta.user\_id = Query1.user\_id

WHERE (((wp\_usermeta.meta\_value) <> '')

AND ((wp\_usermeta.meta\_key) = 'last\_name'));

我尝试通过用括号将Query1的代码括起来,用括号将Query1替换为第二个查询,但这不起作用.我收到此错误：

您的SQL语法有误；请查看与您的MySQL服务器版本相对应的手册,以获取在’.user\_id附近使用正确的语法,(在第22行,选择wp\_usermeta.user\_id,wp\_usermeta.meta\_value AS为“ first”

有人知道怎么做这个吗？谢谢！！

更新

根据@Hogan提供的答案,我想到了：

SELECT

first.user\_id,

first.meta\_value AS 'first\_name',

last.meta\_value AS 'last\_name',

Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_email,

Wp\_woocommerce\_order\_items\_1.order\_item\_name

FROM Wp\_woocommerce\_order\_items

INNER JOIN Wp\_woocommerce\_downloadable\_product\_permissions

ON Wp\_woocommerce\_order\_items.order\_id = Wp\_woocommerce\_downloadable\_product\_permissions.order\_id,

wp\_usermeta AS last

INNER JOIN ((Wp\_woocommerce\_order\_items AS Wp\_woocommerce\_order\_items\_1

INNER JOIN Wp\_woocommerce\_downloadable\_product\_permissions AS Wp\_woocommerce\_downloadable\_product\_permissions\_1

ON Wp\_woocommerce\_order\_items\_1.order\_id = Wp\_woocommerce\_downloadable\_product\_permissions\_1.order\_id)

INNER JOIN wp\_usermeta AS first

ON Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_id = first.user\_id)

ON last.user\_id = Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_id

GROUP BY first.user\_id,

first.meta\_value,

last.meta\_value,

Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_email,

Wp\_woocommerce\_order\_items\_1.order\_item\_name,

Wp\_woocommerce\_order\_items\_1.order\_item\_type,

first.meta\_key,

last.meta\_key

HAVING (((Wp\_woocommerce\_order\_items\_1.order\_item\_type) = 'line\_item')

AND ((first.meta\_key) = 'first\_name')

AND ((last.meta\_key) = 'last\_name'));

它在MS Access中工作得很好,但在SQL中却不能.我收到此错误：

“具有子句”中的未知列“ first.meta\_key”

为什么？

我还必须提到,当我将meta\_key \[s\]添加到SELECT时,如下所示：

SELECT

first.user\_id,

first.meta\_value AS 'first\_name',

last.meta\_value AS 'last\_name',

Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_email,

Wp\_woocommerce\_order\_items\_1.order\_item\_name,

first.meta\_key,

last.meta\_key

我至少没有收到任何错误,查询正在运行,但是鉴于我正在本地主机上工作,因此它会不断增加CPU使用率.

解

SELECT DISTINCT达到目的.没有分组.查询结果出现在127.76毫秒内.这是我的查询：

SELECT DISTINCT

first.user\_id AS 'id',

first.meta\_value AS 'first\_name',

last.meta\_value AS 'last\_name',

Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_email AS 'email',

Wp\_woocommerce\_order\_items\_1.order\_item\_name AS 'order'

FROM Wp\_woocommerce\_order\_items

INNER JOIN Wp\_woocommerce\_downloadable\_product\_permissions

ON Wp\_woocommerce\_order\_items.order\_id = Wp\_woocommerce\_downloadable\_product\_permissions.order\_id,

((Wp\_woocommerce\_order\_items AS Wp\_woocommerce\_order\_items\_1

INNER JOIN Wp\_woocommerce\_downloadable\_product\_permissions AS Wp\_woocommerce\_downloadable\_product\_permissions\_1

ON Wp\_woocommerce\_order\_items\_1.order\_id = Wp\_woocommerce\_downloadable\_product\_permissions\_1.order\_id)

INNER JOIN wp\_usermeta AS first

ON Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_id = first.user\_id)

INNER JOIN wp\_usermeta AS last

ON Wp\_woocommerce\_downloadable\_product\_permissions\_1.user\_id = last.user\_id

WHERE (((first.meta\_key) = 'first\_name')

AND ((last.meta\_key) = 'last\_name')

AND ((Wp\_woocommerce\_order\_items\_1.order\_item\_type) = 'line\_item'));

如@Hogan所述,只需再次使用另一个别名添加表wp\_usermeta,然后进行INNER JOIN.

mysql查询姓氏的_mysql-WordPress SQL查询产品X的用户名和姓氏？

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 Java 获取姓氏的笔画数

相关 Mysql按姓氏从小到大排序的正确sql

相关 sqlite 数据库查询姓氏人口

相关 mysql查询姓氏的_mysql-WordPress SQL查询产品X的用户名和姓氏？

相关 SQL数据库的组合查询和统计查询

相关 SQL数据库的简单查询和连接查询

相关 iOS好友列表根据姓氏分类显示实现

相关 php怎么查询当前用户名和密码,php怎么查询当前用户名

相关 Mysql：查询卡死的sql

相关中国姓氏英文翻译大全