如何在mysql的子查询代码与连接 - how to code sub query in mysql with join

how to code sub query in mysql with join

本文关键字：查询代码连接 mysql | 更新日期: 2023-09-27

我需要从子查询....上的两个表中获取数据我试着问了一些问题，但是……结果未知如何从两个表获取数据相同的id我试过了.....我需要从两个表获取记录lastlogin deatil和登录总数 .......................................................................................

SELECT 
  `name` uname,
  pwd,
  mob,
  dor,
  mob1,
  fld_point,
  fld_count,
  logindetail 
  (SELECT 
    COUNT(*) 
  FROM
    tbl_lastlogin_log 
  WHERE fld_userName = 'all') AS total_visits from fruser
WHERE uname = 'fruser'

我不知道你的表结构，但我可以给你的结构，你需要使用的连接。给你，

，如果您有两个表(table1, table2)，并希望从这两个表中获取一些字段。然后,

select table1.field1, table1.field2,table2.field1, table2.field2 from table1 inner join  table2 on table1.field1 = table2.field1 where table1.field1 = "value";

这将在该条件下连接两个表(table1, table2)。Field1 = table2.field1)。从结果集中，它将使用where condition(table1.field1="value")进行过滤。

想了解更多关于SQL join的信息，使用这个- http://www.w3schools.com/sql/sql_join.asp

从你的问题中不清楚你到底想做什么。但是你可以试试这个:

SELECT col1, col2, col3,
(select cc from another_table at where at.fld_userName = t.uname) as cc,
(select ccc from yet_another_table yat where yat.fld_userName = t.uname) as ccc
from table t
where uname = 'fruser';

正如Saqueib指出的那样，您在查询中缺少FROM。我准备了一个小提琴来演示这个原理:

http://sqlfiddle.com/!2/99d67/3

Table user:

+-------------------+
| UANME | PASSWORD  |
+-------------------+
| userA | secretA   |
| userB | secretB   |
+-------------------+

表lastlogin:

+----------+------------+
| USERNAME | DATE       |
+----------+------------+
| userA    | 2014-01-01 |
| userA    | 2014-01-02 |
| userA    | 2014-01-03 |
| userA    | 2014-01-04 |
| userB    | 2014-02-01 |
| userB    | 2014-02-10 |
+----------+------------+

查询:

SELECT uname, (
               SELECT COUNT(*) FROM lastlogin WHERE username = uname
              ) AS logincount
FROM user;

结果:

+-------+------------+
| UNAME | LOGINCOUNT |
+-------+------------+
| userA | 4          |
| userB | 2          |
+-------+------------+

试试这个，你的查询有语法错误，你的查询是OK的，不需要JOIN

SELECT 
  `name` uname,
   pwd,
   mob,
   dor,
   mob1,
   fld_point,
   fld_count,
   logindetail, //Here you missed the comma
   (SELECT COUNT(*) 
      FROM tbl_lastlogin_log 
    WHERE fld_userName = 'all') AS total_visits 
FROM fruser
    WHERE uname = 'fruser'