mysql计算列

[vcchen_bo_qiang]

mysql数据表有：固定工作、奖金、额外金、扣除、应发工资等字段。怎么在表格实现：应发工资=固定工资+奖金+额外金-扣除

[SQL77]

么意思?

是建表?

SQL

固定工资+奖金+额外金-扣除 AS '应发工资'

[--小F--]

select 应发工资=固定工资+奖金+额外金-扣除 from tb

[vcchen_bo_qiang]

mysql table pwages(id char(4),name char(8),Fixed_wage decimal(8,2),bonus decimal(8,2),Other_add decimal(8,2),Other_cut decimal(8,2),total_wages decimal(8,2));
然后，怎么添加代码，使得total_wages=Fixed_wage + bonus + Other_add - Other_cut 在表中添加，不是在查询时计算。

[--小F--]

MYSQL有请P梁

[SQL77]

mysql table pwages(id char(4),name char(8),Fixed_wage decimal(8,2),bonus decimal(8,2),Other_add decimal(8,2),Other_cut decimal(8,2),Fixed_wage + bonus + Other_add - Other_cut  AS total_wages ); 

这样试试,MYSQL不懂

[dawugui]

[Quote=引用楼主 vcchen_bo_qiang 的回复:]
mysql数据表有：固定工作、奖金、额外金、扣除、应发工资等字段。怎么在表格实现：应发工资=固定工资+奖金+额外金-扣除
[/Quote]
这列貌似乎多余.
查询时直接使用:固定工资+奖金+额外金-扣除.

[SQL77]

[Quote=引用 6 楼 dawugui 的回复:]
引用楼主 vcchen_bo_qiang 的回复:
mysql数据表有：固定工作、奖金、额外金、扣除、应发工资等字段。怎么在表格实现：应发工资=固定工资+奖金+额外金-扣除

这列貌似乎多余.
查询时直接使用:固定工资+奖金+额外金-扣除.

[/Quote]
同意,查询时转换计算就行了

[vcchen_bo_qiang]

[Quote=引用 5 楼 sql77 的回复:]
SQL codemysqltable pwages(idchar(4),namechar(8),Fixed_wagedecimal(8,2),bonusdecimal(8,2),Other_adddecimal(8,2),Other_cutdecimal(8,2),Fixed_wage+ bonus+ Other_add- Other_cutAS total_wages );

这样试试,MYSQL不懂
[/Quote]
这样好像不行，试过了

[vcchen_bo_qiang]

[Quote=引用 6 楼 dawugui 的回复:]
引用楼主 vcchen_bo_qiang 的回复:
mysql数据表有：固定工作、奖金、额外金、扣除、应发工资等字段。怎么在表格实现：应发工资=固定工资+奖金+额外金-扣除

这列貌似乎多余.
查询时直接使用:固定工资+奖金+额外金-扣除.

[/Quote]
确实有点多余，但我想试一下在表中添加。请问，怎么添加呢？

[用户昵称不能为空]

这里高手云集，能不能顺便帮我解决一下这个难题啊
http://topic.csdn.net/u/20091127/16/278761a5-7a37-4f75-8955-0b6eb50c0f2a.html

[ACMAIN_CHM]

update yourTable
set 应发工资=固定工资+奖金+额外金-扣除

[ACMAIN_CHM]

如果想添加的时候自动实现，则需要用触发器，你需要两个触发器 insert / update

CREATE TRIGGER testref BEFORE INSERT ON pwages
  FOR EACH ROW 
  	set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ;

CREATE TRIGGER testref BEFORE UPDATE ON pwages
  FOR EACH ROW 
  	set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ;

[vcchen_bo_qiang]

mysql>create trigger testref before insert on pwages for each row set new.total_wages = new.Fixed_wage + new.bonus + new.other_add + new.other_cut;
Query OK,0 rows affected (0.06 sec)
mysql>create trigger testref1 before update on pwages for each row set new.total_wages = new.Fixed_wage + new.bonus + new.other_add + new.other_cut;
Query OK,0 rows affected (0.06 sec)
mysql>insert into pwages values(1,'张三',3500,1000,500,100,null,'2009-09-09 12:12:12','略');
ERROR 1452(23000):Cannot add or update a child row:a foreign key constraint fails('hrms','pwages',constraint 'pwages_ibfk_1' forerign key ('id') references 'employees' ('id'))

但，
mysql>create table pwages(id char(4),name char(8),Fixed_wage decimal(8,2),bonus decimal(8,2),other_add decimal(8,2),other_cut decimal(8,2),total_wages decimal(8,2),release_date datetime,notes varchar(255));

mysql>delimiter $$
mysql>create trigger testref
mysql>before insert on pwages for each row
mysql>begin
mysql>set new.total_wages=new.Fixed_wage + new.bonus + new.Other_add - new.Other_cut ;
mysql>end;
mysql>$$

Query OK,0 rows affected (0.08 sec)

mysql>insert into pwages values(1,'张三',3500,1000,500,100,null,'2009-09-09 12:12:12','略');
Query OK,1 row affected (0.03 sec)


在没有外键的情况下，用触发器测试成功，但有外键情况不成功。是不是插入记录按表的顺序来插入？

[vcchen_bo_qiang]

-- MySQL dump 10.13 Distrib 6.0.10-alpha, for Win32 (ia32)
--
-- Host: localhost Database: hrms
-- ------------------------------------------------------
-- Server version 6.0.10-alpha-community

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `attendance`
--

DROP TABLE IF EXISTS `attendance`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `attendance` (
`id` char(4) DEFAULT NULL,
`name` char(8) DEFAULT NULL,
`jiaban` int(11) DEFAULT NULL,
`qingjia` int(11) DEFAULT NULL,
`kuanggong` int(11) DEFAULT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`date`),
KEY `id` (`id`),
CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `attendance`
--

LOCK TABLES `attendance` WRITE;
/*!40000 ALTER TABLE `attendance` DISABLE KEYS */;
/*!40000 ALTER TABLE `attendance` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `depts`
--

DROP TABLE IF EXISTS `depts`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `depts` (
`id` char(4) NOT NULL,
`name` char(10) NOT NULL,
`number` int(11) DEFAULT NULL,
`leader` char(8) DEFAULT NULL,
`Tel` char(15) DEFAULT NULL,
PRIMARY KEY (`name`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `depts`
--

LOCK TABLES `depts` WRITE;
/*!40000 ALTER TABLE `depts` DISABLE KEYS */;
/*!40000 ALTER TABLE `depts` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `empchanges`
--

DROP TABLE IF EXISTS `empchanges`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `empchanges` (
`Order_number` int(11) NOT NULL,
`id` char(4) NOT NULL,
`ydept` char(10) NOT NULL,
`zdept` char(10) NOT NULL,
`date` datetime DEFAULT NULL,
`notes` varchar(50) DEFAULT NULL,
PRIMARY KEY (`Order_number`),
KEY `ydept` (`ydept`),
KEY `zdept` (`zdept`),
CONSTRAINT `empchanges_ibfk_1` FOREIGN KEY (`ydept`) REFERENCES `depts` (`name`),
CONSTRAINT `empchanges_ibfk_2` FOREIGN KEY (`zdept`) REFERENCES `depts` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `empchanges`
--

LOCK TABLES `empchanges` WRITE;
/*!40000 ALTER TABLE `empchanges` DISABLE KEYS */;
/*!40000 ALTER TABLE `empchanges` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `employees`
--

DROP TABLE IF EXISTS `employees`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `employees` (
`dept_name` char(10) DEFAULT NULL,
`id` char(4) NOT NULL,
`name` char(8) DEFAULT NULL,
`gender` enum('ÄÐ','Å®') NOT NULL,
`birth` datetime DEFAULT NULL,
`national` char(6) DEFAULT NULL,
`Pol` enum('µ³Ô±','ÍÅÔ±','ȺÖÚ') NOT NULL,
`Marital` enum('ÒÑ','δ') NOT NULL,
`address` varchar(50) DEFAULT NULL,
`join_time` enum('Ñо¿Éú','±¾¿Æ','´óר','¸ßÖ°','ÖÐר','ÆäËû') NOT NULL,
`profession` enum('½ÌÊÚ','¸±½ÌÊÚ','½²Ê¦','Öú½Ì') NOT NULL,
`Tel` char(15) DEFAULT NULL,
`ID_number` char(18) DEFAULT NULL,
`resume` varchar(200) DEFAULT NULL,
`notes` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `dept_name` (`dept_name`),
CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `depts` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `employees`
--

LOCK TABLES `employees` WRITE;
/*!40000 ALTER TABLE `employees` DISABLE KEYS */;
/*!40000 ALTER TABLE `employees` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `incentive`
--

DROP TABLE IF EXISTS `incentive`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `incentive` (
`Order_number` int(11) NOT NULL,
`id` char(4) DEFAULT NULL,
`name` char(8) DEFAULT NULL,
`description` char(50) DEFAULT NULL,
`type` enum('½±','³Í') DEFAULT NULL,
`date` datetime DEFAULT NULL,
PRIMARY KEY (`Order_number`),
KEY `id` (`id`),
CONSTRAINT `incentive_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `incentive`
--

LOCK TABLES `incentive` WRITE;
/*!40000 ALTER TABLE `incentive` DISABLE KEYS */;
/*!40000 ALTER TABLE `incentive` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `pwages`
--

DROP TABLE IF EXISTS `pwages`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `pwages` (
`id` char(4) DEFAULT NULL,
`Fixed_wage` decimal(8,2) DEFAULT NULL,
`bonus` decimal(8,2) DEFAULT NULL,
`other_add` decimal(8,2) DEFAULT NULL,
`other_cut` decimal(8,2) DEFAULT NULL,
`total_wages` decimal(8,2) DEFAULT NULL,
`date` datetime NOT NULL,
`notes` varchar(50) DEFAULT NULL,
PRIMARY KEY (`date`),
KEY `id` (`id`),
CONSTRAINT `pwages_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `pwages`
--

LOCK TABLES `pwages` WRITE;
/*!40000 ALTER TABLE `pwages` DISABLE KEYS */;
/*!40000 ALTER TABLE `pwages` ENABLE KEYS */;
UNLOCK TABLES;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger testref before insert on pwages for each row
begin
set new.total_wages=new.Fixed_wage + new.bonus + new.other_add - new.other_cut;
end */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 trigger testref2 before update on pwages for each row
begin
set new.total_wages=new.Fixed_wage + new.bonus + new.other_add - new.other_cut;
end */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;

--
-- Table structure for table `wagefile`
--

DROP TABLE IF EXISTS `wagefile`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `wagefile` (
`id` char(4) DEFAULT NULL,
`name` char(8) DEFAULT NULL,
`basic` decimal(8,2) DEFAULT NULL,
`post` decimal(8,2) DEFAULT NULL,
`subsdies` decimal(8,2) DEFAULT NULL,
`fund` decimal(8,2) DEFAULT NULL,
KEY `id` (`id`),
CONSTRAINT `wagefile_ibfk_1` FOREIGN KEY (`id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;

--
-- Dumping data for table `wagefile`
--

LOCK TABLES `wagefile` WRITE;
/*!40000 ALTER TABLE `wagefile` DISABLE KEYS */;
/*!40000 ALTER TABLE `wagefile` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2009-11-29 8:18:58

[江南昆虫]

mssql的话在字段定义中可以加入公式，mysql好像不支持。

那还是用触发器计算吧，或者写个视图，但本质上和查询时写公式也差不多

[vcchen_bo_qiang]

我觉得触发器有触发器好，查询有查询的好。弄明白了，插入记录要按表的顺序插入的。非常感谢各位高手帮助！我插入数据以*.txt文本的形式导入表中。

[ACMAIN_CHM]

[Quote]在没有外键的情况下，用触发器测试成功，但有外键情况不成功。是不是插入记录按表的顺序来插入？[/Quote]

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。