主页 / dba / 问题

问题[mysqli](dba)

Martin Hope
pankaj
Asked: 2021-03-16 10:16:44 +0800 CST
  • 1
SELECT u.*,h.*,d.* 
FROM User u
LEFT JOIN HealthDec h ON u.ID = h.EmpId
LEFT JOIN Document d On u.ID = d.UserID;

和：

SELECT count(id) FROM OHS 
WHERE Category = 'POLICIES' 
  AND ID NOT IN 
  (
    SELECT OHSID 
    FROM User_OHS 
    WHERE UserID='1' AND Type='POLICIES'
  );

两个查询都可以根据需要分别正常工作。但我想结合这两个查询来为每个用户获取结果。

表结构

mysql mysqli
Martin Hope
Marco
Asked: 2019-01-12 13:49:16 +0800 CST
  • 1

我有一个 am:n（多对多）关系表。基本上，如果我选择一个article_id，我想返回所有其他tag_id与所选 相同的文章article_id。

| article_id | tag_id |
|------------|--------|
| 1          | 2      |
| 1          | 11     |
| 2          | 5      |
| 4          | 6      |
| 4          | 11     |
| 12         | 9      |
| 14         | 9      |
| 14         | 15     |
| 14         | 17     |
| 18         | 1      |
| 20         | 8      |
| 41         | 13     |
| 49         | 9      |
| 50         | 12     |
| 60         | 18     |
| 75         | 9      |
| 75         | 10     |
| 75         | 11     |

所以，如果我选择article_id 75，我需要输出所有包含tag_id 9、10 和 11的文章

| article_id | tag_id |
|------------|--------|
| 1          | 11     |
| 4          | 11     |
| 12         | 9      |
| 14         | 9      |
| 49         | 9      |

我创建了以下查询，它看起来有效，但我想确认一下。有没有更好的办法？

SELECT aj2.article_id, aj2.tag_id
FROM article_tag_join aj
JOIN article_tag_join aj2 ON aj2.tag_id = aj.tag_id
WHERE aj.article_id = 75 AND aj2.article_id <> 75
mysql mysqli
Martin Hope
Mangesh
Asked: 2018-11-01 11:05:31 +0800 CST
  • 1

在此处输入图像描述 这是我的代码

SELECT   
              register cr.id,
              register_cr.first_name,
              register_cr.father_name,
              register_cr.surname,
              register_cr.address,
              cr_record.charge,
              cr_record.date,
              cr_record.charge_type,
              cr_record.fir_no
FROM 
             register_cr as register_cr
INNER JOIN 
             cr_record as cr_record ON register_cr.id = cr_record.cri_reco_id
mysql mysqli
Martin Hope
Web Infos
Asked: 2018-01-24 04:54:57 +0800 CST
  • 1

假设我有一个多次运行的查询，因为它在一个将数据插入表的 while 循环中。

INSERT INTO <table>( psuiKey, group_id) VALUES ('54ds8a784daEd','1');

问题是当来自不同计算机的两个用户同时添加数据时。所以发生的事情是用户 1将数据添加到表中，如下所示：

psuiKey       group_id 
54ds8a784daEd  1
54ds8a785daEd  1

同时用户 2在2group_id处添加数据，但数据如下所示：

psuiKey       group_id 
54ds8a784daEd  1
54ds8a785daEd  1
54ds8a786daEd  1
54ds8a787daEd  1

代替： 

psuiKey       group_id 
54ds8a784daEd  1
54ds8a785daEd  1
54ds8a786daEd  2
54ds8a787daEd  2

所以简而言之，如果两个用户同时添加相同的数据，那么group_id当我发现 maxgroup_id使用时不会改变：

SELECT MAX(group_id) + 1

因此，当两个用户同时添加数据时，SQL 无法更新它。

注意我不能用group_id作主键，我也使用datetime.

所以任何帮助都会有所帮助！

mysql mysqli
Martin Hope
zeeshan
Asked: 2015-04-19 10:33:22 +0800 CST
  • 0

我有这个查询：

SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) 
from wp_result_plugin 
WHERE Bet=2 
AND month(`Date`) = EXTRACT(month FROM (NOW())) 
ORDER BY MONTH(`Date`) ASC

UNION 

SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) 
from wp_result_plugin 
WHERE Bet=2 
AND month(`Date`) 
ORDER BY MONTH(`Date`) ASC

我看到这个错误：

Incorrect usage of UNION and ORDER BY

如何使这项工作？

mysql-5.5 mysqli
Martin Hope
Alireza
Asked: 2012-04-26 23:43:22 +0800 CST
  • 1

我有一个帖子表如下： 

CREATE TABLE `post` (
  `p_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` smallint(5) unsigned NOT NULL,
  `cat_id` tinyint(3) unsigned NOT NULL,
  `view_count` smallint(5) unsigned NOT NULL DEFAULT '0',
  `status` tinyint(3) unsigned NOT NULL,
  `abstract` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `title` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `meta_description` tinytext CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `picture` varchar(100) NOT NULL,
  PRIMARY KEY (`p_id`),
  KEY `user_id` (`user_id`),
  KEY `date` (`date`),
  KEY `pic_id` (`picture`),
  KEY `cat_id` (`cat_id`),
  CONSTRAINT `post_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `karbar` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `post_ibfk_3` FOREIGN KEY (`cat_id`) REFERENCES `category` (`cat_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1

我有一个评论表如下： 

CREATE TABLE `comment` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `p_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `name` varchar(50) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL DEFAULT '',
  `site` varchar(200) CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `content` text CHARACTER SET ucs2 COLLATE ucs2_persian_ci NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`),
  CONSTRAINT `comment_ibfk_1` FOREIGN KEY (`p_id`) REFERENCES `post` (`p_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

我想获得一篇文章（例如 p_id=8）及其所有评论。怎么做？如何在获取帖子记录的同时从评论表中获取计数（*）？

count mysqli